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!!

Hi,

The 2 minus signs (double unary) covert TRUE and FALSE into 1 and 0

Mike

"SykesvilleJim" wrote:

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!!

Sorry,

I meant to supply a link giving the full explanation. Look here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Mike

"Mike H" wrote:

Hi,

The 2 minus signs (double unary) covert TRUE and FALSE into 1 and 0

Mike

"SykesvilleJim" wrote:

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!!

Please refer

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--
If this post helps click Yes
---------------
Jacob Skaria


"SykesvilleJim" wrote:

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!!

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 whe

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. :-)

"Mike H" wrote:

Sorry,

I meant to supply a link giving the full explanation. Look here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Mike

"Mike H" wrote:

Hi,

The 2 minus signs (double unary) covert TRUE and FALSE into 1 and 0

Mike

"SykesvilleJim" wrote:

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!!