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?
witzph1Asked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Missing some parens:

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

Kevin
0
 
witzph1Author 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
All Courses

From novice to tech pro — start learning today.