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.

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

Which range?

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

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
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.

Sorry.

The range BOX!I5:I280

"T. Valko" wrote:

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

Which range?

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

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
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.


.

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.

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
Sorry.

The range BOX!I5:I280

"T. Valko" wrote:

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

Which range?

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

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
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.


.

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
Sorry.

The range BOX!I5:I280

"T. Valko" wrote:

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

Which range?

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

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
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.


.