Sumifs

[galvinpaddy]

Hi All,

I have used the following formula in one of my spreadsheets, now i know it works, but i dont quite understand how or why.

=SUMPRODUCT(--(Record!$E:$E=$Y$3),--(Record!$L:$L=$U2),Record!$AK:$AK)

Record!$E$E:$E$E=$Y$3 & Record!$L:$L=$U2),Record!$AK:$AK)
i understand as they are the sheet page ref's and the ranges, but what on earth are the (--( for??

Thanks

[tigeravatar]

galvinpaddy,

Record!$E$E:$E$E=$Y$3 is a logical test that returns boolean values TRUE or FALSE. In order for the SumProduct formula to use that information, it has to be converted into numbers. Namely 1 for TRUE and 0 for FALSE. This is why the -- is there. It can be read as "minus negative" to return a numeric value.

You'll notice that if you input into a cell =--TRUE the cell will show as 1 and =--FALSE will show as 0. That's because Excel interprets those boolean values to the desired outcome. If you tried =--"A" you would get an error.

The same results could be achieved with 1* or 0+, but -- looks a little better and keeps the components of the sumproduct formula clearly separated so it has kind of become the norm.

[galvinpaddy]

please mark as solved
Thankyou