x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 287

# SUMPRODUCT test for blank cells

I've got a spreadsheet in which I need to count the number of names in C12:C309 where column E is not blank and column F does not have "DUP" in it.  I've created a sumproduct formula below:

=SUMPRODUCT(C12:C309<>"")*(E12:E309<>"")*(F12:F309<>"DUP")

This formula should work perfectly in theory, and in fact does do what I want on a "normal" spreadsheet with static values in columns C, E and F.  But in my "real" spreadsheet, the values in Columns C, E and F are pulling from another linked spreadsheet and only "appear" blank or empty through the use of an IF statement (=IF<linkfield>="","",<linkfield>). In other words, all rows 12 to 309 have an IF statement in them.

So somehow I need to have the SUMPRODUCT fn NOT count the row if the calculated value of each field is blank.  Makes sense?
0
witzph1
1 Solution

ConsultantCommented:
Missing some parens:

=SUMPRODUCT((C12:C309<>"")*(E12:E309<>"")*(F12:F309<>"DUP"))

Kevin
0

Author Commented:
Holy cow.  Pretty sad that I didn't catch that.  But thanks for you help.  That was an easy 500 points.  I guess it's 1 point for the parenthesis and 499 for knowing that it was the parenthesis.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.