SUMPRODUCT Issue (Counting Blank Cells)

[JPV88]

Greetings all! First Time poster,
I am having an issue with a SUMPRODUCT formula to where the the outcome is counting all the blank cells. I have read through many posts about adding the same range with a (,<>"") at the end and it only produces a #VALUE error as a result. Below is my formula with an explanation of what I am trying to do.

=IF($BA8=$A$6,SUMPRODUCT(--($K$6:$K$200=$L$6:$L$200)),0)

I am working a Verification document for my job. We forecast weather events for aircraft and need to verify whether it is forecasted and if it occurs. These are answered with a Y or N.

$BA8 is a name of a forecaster and need to make a true value if it is equal with the same name is $A$6.

$K$6 is the first block with a Y or N
$L$6 is the second block with a Y or N.

If both of these values are equal, then I need it to get counted within the cell that I place the formula.

Right now I got as far as a working formula but it counts all of the blanks and gives me a total of 195 after counting the entire range of cells.

I feel that this is an easy solution but I have been attempting to comb through other posts for hours trying to piece together a working formula, to no avail.

Any assitance is appreciated, please follow up with any questions if anything isnt clear!

Thank you for your time!

[jason.b75]

The thread that you read suggesting <>"" is on the right lines, but there are 2 ways that it can be applied in sumproduct.

=IF($BA8=$A$6,SUMPRODUCT(($K$6:$K$200=$L$6:$L$200)*($K$6:$K$200<>"")),0)

As an alternative, you could also use countifs to count the occurences of Y / N

=IF($BA8=$A$6,SUM(COUNTIFS($K$6:$K$200,{"Y","N"},$L$6:$L$200,{"Y","N"})),0)

[JPV88]

The first one works perfectly, and thanks for the alternative option as well! I was missing the *, and was using a , instead.

That was a big help! Thank you!

[jason.b75]

It should work with comma as well, maybe you had the syntax slighlty wrong.

=IF($BA8=$A$6,SUMPRODUCT(--($K$6:$K$200=$L$6:$L$200),--($K$6:$K$200<>"")),0)

There are pros and cons to both methods. the Multiplier method is the most versatile, but there are occasions where the comma method is needed, the main one that comes to mind is totalling values that are in a range mixed with text.