SUMIF with multiple criteria
in [Functions]
|
Prev: Using a cell's value to copy another cells info to another workshe
Next: don't want to have to change font in header and footer each time?
From: T. Valko on 16 Mar 2010 21:09 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 > |