Modify a Formula

C

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

T. Valko

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

Which range?

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top