Sumproduct - Avoiding blanks

[jpruffle]

Hi,

I have the following formula:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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