Modify a Formula

[carl]

Hello All.

I use this formula
=SUMPRODUCT(--(BOX!A5:A280=Sheet1!B12);--(BOX!I5:I280=TRUE))/COUNTIF(BOX!A5:A280;Sheet1!B12)

The range that it is looking at has values like #NUM! and the formula
returns #NUM!.

Is there a way to modify the formula so that it ignores the #NUM! ?

Thanks you in advance.

 

[T. Valko]

The range that it is looking at has values like #NUM!

Which range?

BOX!A5:A280
BOX!I5:I280
Sheet1!B12

 

[carl]

Sorry.

The range BOX!I5:I280

Which range?

BOX!A5:A280
BOX!I5:I280
Sheet1!B12

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

Try this array formula** :

=SUM(IF(Box!A5:A280=Sheet1!B12,IF(ISLOGICAL(Box!I5:I280),IF(Box!I5:I280=TRUE,1))))/COUNTIF(Box!A5:A280,Sheet1!B12)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Improvement...

We can shorten that a bit and save a few keystrokes:

=SUM(IF(Box!A5:A280=Sheet1!B12,IF(ISLOGICAL(Box!I5:I280),IF(Box!I5:I280,1))))/COUNTIF(Box!A5:A280,Sheet1!B12)

Still array entered!

Also, you might have to replace the commas with semi-colons as argument
separators depending on your international location.