What does using -- in a formula do?

S

SykesvilleJim

I was helped yesterday with creating a complex formula using the SUMPRODUCT
function. It worked GREAT and I thank you VERY much. I mostly understand how
it works, but I did not understand the use of -- in it. Here is the formula:

=SUMPRODUCT(--(Dates!B$4:B$21=A13),--(B13>=Dates!C$4:C$21),--(B13<=Dates!D$4:D$21),Dates!E$4:E$21)

Can you explain the formulas use of the "--"'s? Thanks!!
 
S

Sean Timmons

And perhaps to just explain fully what the below formula is saying...

=SUMPRODUCT(--(Dates!B$4:B$21=A13),--(B13>=Dates!C$4:C$21),--(B13<=Dates!D$4:D$21),Dates!E$4:E$21)

Means..

For each row of sheet named Dates from 4 - 21 where:

column B equals cell A13 of our sheet
AND
column C is less than or equal to B13 of our sheet
AND
column D is greater than or equal to B13 of our sheet

sum the values in column E

The calculation gives 1 to each that meet the criteria when -- is present.
SUMPRODUCT multiplies all of the arrays together for each row, then adds the
rows together.
Since 0 times any number is 0, having a 0 in any of the arrays returns for
that row
since 1 times any number is itself, any row that meets all criteria will be
1*1*1*calue in column D.

Then, every row is added together... all your 0's, then all your resulting
D's...

Hope that was broken down enough. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top