Formula Explanation Excel 2016 "--"

Question

I was doing research in how to use a formula and someone provided me with the following: =SUMPRODUCT(--(YEAR(A2:A15)=2010),--(B2:B15>=50))

The person has not responded in over a week and I was wondering if anyone could explain the purpose of using the -- before the ranges? Is this in relation to using Ctrl+Shift+Enter?

This was answered a while ago over on Superuser: [https://superuser.com/a/1089109](https://superuser.com/a/1089109) (since that is a different Stack Exchange site than Stack Overflow, I'm just adding the link here instead of downvoting). — tysonwright, Feb 21 '18 at 01:12

score 2 · Accepted Answer · answered Feb 21 '18 at 01:08

2

-- turns a range of logical values (e.g. TRUE/FALSE) to a range of numerical values (e.g. 1/0). This is necessary in order for SUMPRODUCT to work as intended. It has nothing to do with Ctrl+Shift+Enter.

Another alternative is to use +0, e.g.

=SUMPRODUCT((YEAR(A2:A15)=2010)+0,(B2:B15>=50)+0)

In this case, not even necessary to do either. Could just do this:

=SUMPRODUCT((YEAR(A2:A15)=2010)*(B2:B15>=50))

All three of these alternatives will return the same result.

answered Feb 21 '18 at 01:08

ImaginaryHuman072889

4,953
7
19
51

Awesome! Thank you, it may be useful in other formula like an if statement using a `Sum(If(...))` – MartyMcfly0033 Feb 21 '18 at 01:12
There's a ton of examples on SO that use this `--` or `+0`. Wouldn't take too long of searching through the "excel-formula" tag to find some examples. – ImaginaryHuman072889 Feb 21 '18 at 01:13

Formula Explanation Excel 2016 "--"

1 Answers1