SUMPRODUCT and Blank Cells

[imback2nite]

Hello everyone and thank you for looking at this question. I’m using, or should I say, attempting to use SUMPRODUCT.
I’m trying to use this formula. The problem I’m having is there are blank cells in columns ‘A’, ‘B’, ‘C’ and the ‘D:AA’. How can I negate the blank cells?

=SUMPRODUCT(--(Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

 

[Phuoc]

Try again:

=SUMPRODUCT(N(+Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

 

[Peter_SSs]

We are trying to help. Makes it hard if you do not answer the questions being asked.

The result of my formula is the sum of the green cells above. Is that what you would be expecting?

Also, you did not mention originally what was going wrong with your formula. Was it returning a #VALUE! error like below?

I'm sorry. Still getting 0.

With what formula and what data?

 

[imback2nite]

Try again:

=SUMPRODUCT(N(+Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

I apologize for the late reply but the =SUMPRODUCT(N(+ worked!
What

Try again:

=SUMPRODUCT(N(+Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

Thank you! Thank you!!
I apologize for the late reply. The =SUMPRODUCT(N(+ seems to have worked! What exactly is the (N(+ ? Is that what negates the empty cells?

 

[imback2nite]

I apologize for the late reply but the =SUMPRODUCT(N(+ worked!
What

Thank you! Thank you!!
I apologize for the late reply. The =SUMPRODUCT(N(+ seems to have worked! What exactly is the (N(+ ? Is that what negates the empty cells?

My fault! I have one more criteria. I realize SUMPRODUCT is going by rows but can I include a horizontal criteria? I only want to SUM if the two criteria (Chart!$D$5 and Chart!$E$5) are met. Can I use another criteria? (Finances!$E$12) This criteria will span the Finances page from ($D$34:$AA$34).
I"m using the formula
=IF(AND(Chart!$C5="",Chart!$D5=""),"",IF(AND(Chart!$C5<>"",Chart!$D5=""),SUMPRODUCT(N(+Finances!$D$35:$AA$999)*(Finances!$A$35:$A$999=Chart!$C5)*(Finances!$C$35:$C$999=Finances!$C$13)),IF(AND(Chart!$C5<>"",Chart!$D5<>""),SUMPRODUCT(N(+Finances!$D$35:$AA$999)*(Finances!$A$35:$A$999=Chart!$C5)*(Finances!$B$35:$B$999=Chart!$D5)*(Finances!$C$35:$C$999=Finances!$C$13)),"")))

 

[imback2nite]

I figured it out! Thanks to everyone!!!
=IF(AND(Chart!$C7="",Chart!$D7=""),"",IF(AND(Chart!$C7<>"",Chart!$D7=""),SUMPRODUCT(N(+Finances!$D$37:$AA$1001)*(Finances!$A$37:$A$1001=Chart!$C7)*($D$36:$AA$36=$E$12)*(Finances!$C$37:$C$1001=Finances!$C$13)),IF(AND(Chart!$C7<>"",Chart!$D7<>""),SUMPRODUCT(N(+Finances!$D$37:$AA$1001)*(Finances!$A$37:$A$1001=Chart!$C7)*($D$36:$AA$36=$E$12)*(Finances!$B$37:$B$1001=Chart!$D7)*(Finances!$C$37:$C$1001=Finances!$C$13)),"")))