From: T. Valko on
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438(a)uwe> wrote in message
news:a51dcee14aa2d(a)uwe...
> Wow! I used to feel intelligent ..... ;-)
>
> This may take a while for my mind to get around.
>
> Thank you.
>
> I appreciate the help.
>
> GBExcel
>
> T. Valko wrote:
>>>What does the double minus sign, (as in --), in the formula do?
>>
>>SUMPRODUCT multiplies arrays of numbers together then sums the results of
>>that muliplication to return the result. In the formula we've used some
>>logical tests and we have to convert those results into numeric values
>>that
>>SUMPRODUCT can then process.
>>
>>Here's how that happens...
>>
>>These expressions will return an array of either TRUE or FALSE:
>>
>>TEXT(YEARNow!B9:B100,"mmm")=BB23
>>YEARNow!E9:E100="Personal"
>>
>>Let's assume this is a small sample of your data:
>>
>>Per = Personal
>>Bus = Business
>>
>>Jan 01...Per...10
>>Feb 11...Per...15
>>Jan 08...Bus...12
>>Mar 10...Per...10
>>Jan 03...Per...10
>>
>>TEXT(cell_ref,"mmm") returns the short month name as a text string from a
>>date. So:
>>
>>TEXT(Jan 01,"mmm") = Jan
>>TEXT(Feb 11,"mmm") = Feb
>>TEXT(Jan 08,"mmm") = Jan
>>TEXT(Mar 10,"mmm") = Mar
>>TEXT(Jan 03,"mmm") = Jan
>>
>>We're testing to see if those month names = BB23 which holds the month
>>name
>>Jan:
>>
>>T = TRUE
>>F = FALSE
>>
>>Jan = Jan = T
>>Feb = Jan = F
>>Jan = Jan = T
>>Mar = Jan = F
>>Jan = Jan = T
>>
>>The double unary -- converts those logical values to either 1 or 0:
>>
>>--TRUE = 1
>>--FALSE = 0
>>
>>--(TEXT(YEARNow!B9:B100,"mmm")=BB23)
>>
>>Now we have an array of 1s and 0s:
>>
>>{1;0;1;0;1}
>>
>>The same thing is done with:
>>
>>YEARNow!E9:E100="Personal"
>>
>>Per = Per = T
>>Per = Per = T
>>Bus = Per = F
>>Per = Per = T
>>Per = Per = T
>>
>>--(YEARNow!E9:E100="Personal")
>>
>>{1;1;0;1;1}
>>
>>Now we have 3 arrays of numbers that can be multiplied together:
>>
>>SUMPRODUCT({1;0;1;0;1},{1;1;0;1;1},{10;15;12;10;10})
>>
>>Vertically:
>>
>>1*1*10 = 10
>>0*1*15 = 0
>>1*0*12 = 0
>>0*1*10 = 0
>>1*1*10 = 10
>>
>>Then the results of the multiplication are summed:
>>
>>SUMPRODUCT({10;0;0;0;10}) = 20
>>
>>So, based on the sample data:
>>
>>=IF(BA24="Pay
>>slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")
>>
>>=20
>>
>>See this for more on SUMPRODUCT:
>>
>>http://xldynamic.com/source/xld.SUMPRODUCT.html
>>
>>exp101
>>> Yes! Yes! Yes!
>>>
>>[quoted text clipped - 18 lines]
>>>>>
>>>>> GBExcel
>
> --
> Message posted via http://www.officekb.com
>