CountIf or SumProduct except when variable is present

[willdc]

I am not finding a relevant thread already posted so hopefully someone can solve this. I am creating a dashboard of issues and creating a graph from the results.

=SUMPRODUCT(('Issue Log'!B3:B608="Corrective Action")+0)
is the same as
=COUNTIF('Issue Log'!B3:B608,B3) where B3 is Corrective Action

however I need to exclude from the count any row which also has any data in the J column

I have tried
=SUMPRODUCT(('Issue Log'!B3:B608="Corrective Action"),('Issue Log'!J3:J608<>"")+0)
but that returns a value of zero regardless of data in the Issue Log. Any ideas on what I am doing wrong?

thanks,Will

 

[UniMord]

If you're using Excel 2007 or later, the best way is with COUNTIFS, which is the same as COUNTIF, except it allows multiple criteria. So, in your case, the formula would be:
=COUNTIFS(B3:B608, "Corrective Action", J3:J608, "")

If you're using an older version of Excel, you can use:
=SUMPRODUCT((B3:B608="Corrective Action")*(J3:J608=""))
which, is close to what you were doing.

Or =SUM(IF((B7:B17="Corrective Action")*(J7:J17=""), 1, 0))
(Confirm that with Ctrl+Shift+Enter.)