SUMPRODUCT and "--", what does it do?

[dengel3587]

I was having trouble with COUNTIF, so I fell upon this old thread:
http://www.mrexcel.com/forum/excel-questions/84490-countif-not-counting-all-values.html
In the third reply, fairwinds suggests using "=SUMPRODUCT(--(A1:A10={"y","n","n/a"}))" instead of COUNTIF.

For reasons I don't know, using that "--( )" fixed my problem. Specifically, my problem was I was trying to count the number of times "<-50%" appeared in some cells. The list looked something like this: [TABLE="width: 200"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]-20 to -25%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]+20 to +25%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0 to +5%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]<-50%[/TD]
[/TR]
</TBODY>[/TABLE]
Using
=COUNTIF($A$1:$A$5, <criteria> )
worked for things like "0%" and "-20 to -25%", but it didn't work for "<-50%". Then when I used
=SUMPRODUCT(--($A$1:$A$5="<-50%"))
it worked. What does the "--( )" do that makes this^ formula work, but not the COUNTIF or using the SUMPRODUCT without the "--( )" part.

 

[gsistek]

1.) Welcome to the forum.
2.) The '--' is just what it may seem, a double negative. Since the statement in the SUMPRODUCT function is returning TRUE or FALSE, these values cannot be multiplied and added properly since the function is expecting numbers. We use a double negative to convert TRUE to 1 and FALSE to 0 (binary) so the function can use them.
3.) You could still use COUNTIF with the < -50%. The issue is that Excel needs comparison operators (such as < , > , <> , < =, etc) enclosed in quotes to use correctly. You would need =COUNTIF($A$1:$A$5,"<-50%"). If you want to use a reference instead of hard-coding -50%, you need to join the operator and the reference together using & - ie: =COUNTIF($A$1:$A$5,"<"&$B$1)

 

[dengel3587]

I'm not trying to count if a number is less than 50%, I'm trying to count if the cell contains the text "<-50%", so =COUNTIF($A$1:$A$5,"<"&$B$1) is not what I would want to do. However, I think the fact that Excel uses "<" to compare values may have something to do with why it wasn't working. Maybe it was considering the "<" in "<-50%" as an operator instead of just text.

But thanks for your explanation of "--". That was the main thing I wanted to know.

 

[gsistek]

Ah. I misunderstood. I see now. You are correct, the COUNTIF was interpretting "<-50%" as 'Values less than negative 50 percent' instead of the text string. I'm sure there is a work around (my mind is trying to shut down - end of the work day), but I think its pretty safe to say that the SUMPRODUCT approach is cleaner and easier.