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:

	A
1	0%
2	-20 to -25%
3	+20 to +25%
4	0 to +5%
5	<-50%

<TBODY>
</TBODY>

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.