Countif w/ #VALUE! errors

[drvortex]

I'm so close to completion on this project. One problem I'm having.

=IF(COUNTIF($C$9:$C$33,B42)>0,SUMPRODUCT(--($C$9:$C$33=B42),--($L$9:$L$33)))

Take this formula above. The following table looks like this:

C9:C33 displays names
B42 = one specific name
L9:L33 displays TRUE or FALSE answers

However, the array L9:L33 and C9:C33 may change for each month
(pertaining to each worksheet). The above formula WORKS if the ALL the
boxes in L9:L33 array is filled in w/ TRUE or FALSE. However, what do I
need to add to the formula in case the box is *blank*.

I thought of using the ISERROR, ISERR, ISNA, or something else to have
the formula NOT COUNT the blank box. Been working on this all day and
nothing seems to work. Doesn't seem difficult to fix but nothing is
working.

 

[Roger Govier]

Hi

Maybe
=IF(COUNTIF($C$9:$C$33,B42)>0,SUMPRODUCT(--($C$9:$C$33=B42),--($L$9:$L$33),--($L$9:$L$33<>"")))

Regards

Roger Govier

 

[Peo Sjoblom]

Since only TRUE will be counted (or rather summed since that is what you are
doing but if summed it's value is 1, FALSE is zero so it won't be counted)
you can use

SUMPRODUCT(--($C$9:$C$33=B42),--($L$9:$L$33=TRUE))

for the sumproduct part, that way you count TRUE in L where C is B42

 

[drvortex]

Wow...you are amazing. It worked and can't believe it that all I had to
add was the "=TRUE" in one location and BAM it works. I added that in
my three other formulas and TADA...it works. This saves a ton of time
and greatly improves the metrics of our flight. Thanks again!!!

 

[Peo Sjoblom]

Thanks for the feedback

--
Regards,

Peo Sjoblom

(No private emails please)