Sumproduct - Avoiding blanks

[jpruffle]

Hi,

I have the following formula:

=SUMPRODUCT((name=C4)*(dob=E4)*(MONTH(month)=MONTH(B4)))

This bascially checks how many occurances a youth (based on name and dob) has been reported this momnth (Month). The only problem is with this is it calculates blank entries. I have copied down the formula to 500 rows so it shows 500 occurances of the blanks.

So is there any way to hide blank entries to prevent them showing as a total?

Thank you in advance,

JP

[ChemistB]

Without seeing your spreadsheet, it's hard to be sure but if you add an expression which checks for blanks, it should work. For example

=SUMPRODUCT((name=C4)*(dob=E4)*(MONTH(month)=MONTH(B4))*(C4>0))

Does that work for you?

ChemistB

[NBVC]

or perhaps?

=SUMPRODUCT((name=C4)*(dob=E4)*(MONTH(month)=MONTH(B4))*(month<>""))

[jpruffle]

Thank you both, NBVC I went with yours. I have a similar formula:

=SUMPRODUCT(--(name=C4)*--(dob=E4))

Which is also filled down 500 rows, once again it calculates blank entries how can I omit blanks?

hank you in advance,

JP

[NBVC]

That one I would think would count the blanks if E4 were empty...

Perhaps this might clean it up a bit?

=SUMPRODUCT((name=C4)*(dob=E4)*(dob<>""))

[jpruffle]

Thanks that worked great.

Really appreciated,

JP