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: GBExcel via OfficeKB.com on 16 Mar 2010 16:29 Hi, I need help to SUMIF, subject to several criteria being met. Here is the setup: 1. I need a formula for cell BB24. The header (cell BB23 contains the word; Jan) 2. To the left of cell BB24 is BA24, which contains the phrase; Pay Slip. 3. Now we go to worksheet called; YEARNow. Column B9:B100 contains dates in the format MMM DD, as in Jan 01, Feb 10, Mar 13, etc. 4. In the same worksheet is a column E9:E100, which contains either the word; Business, or the word; Personal in each of its rows. 5. The values that need to be SUMIF'd are in the YEARnow worksheet in column H9:H100 The formula in cell BB24 (See 1.), needs to evaluate the above and reflect the value derived from a SUMIF of the YEARnow worksheet column H9:H100 according to the following criteria: 1. If BB24 contains the phrase, Pay Slip, (See 2.), 2. And if the YEARNow. Column B9:B100 contains months that match the cell header BB23, which in this case is the month; Jan, (See 1.), 3. And if the YEARNow E9:E100, contains the word; Personal, (See 4.). I've tried SUMIF in various formats, but keep getting a '0' anser when I should be getting a value. Appreciate the help. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201003/1
From: T. Valko on 16 Mar 2010 16:50 Try this... =IF(BA24="Pay slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"") -- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438(a)uwe> wrote in message news:a51c182ab8f15(a)uwe... > Hi, > > I need help to SUMIF, subject to several criteria being met. > > Here is the setup: > > 1. I need a formula for cell BB24. The header (cell BB23 contains the > word; > Jan) > 2. To the left of cell BB24 is BA24, which contains the phrase; Pay Slip. > > 3. Now we go to worksheet called; YEARNow. Column B9:B100 contains dates > in > the format MMM DD, as in Jan 01, Feb 10, Mar 13, etc. > 4. In the same worksheet is a column E9:E100, which contains either the > word; > Business, or the word; Personal in each of its rows. > 5. The values that need to be SUMIF'd are in the YEARnow worksheet in > column > H9:H100 > > The formula in cell BB24 (See 1.), needs to evaluate the above and reflect > the value derived from a SUMIF of the YEARnow worksheet column H9:H100 > according to the following criteria: > > 1. If BB24 contains the phrase, Pay Slip, (See 2.), > 2. And if the YEARNow. Column B9:B100 contains months that match the cell > header BB23, which in this case is the month; Jan, (See 1.), > 3. And if the YEARNow E9:E100, contains the word; Personal, (See 4.). > > I've tried SUMIF in various formats, but keep getting a '0' anser when I > should be getting a value. > > Appreciate the help. > > GBExcel > > -- > Message posted via OfficeKB.com > http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201003/1 >
From: GBExcel via OfficeKB.com on 16 Mar 2010 17:05 Yes! Yes! Yes! I'm so excited -- it really works! :-) Thank you. It was giving me a headache. Can I ask a further question; What does the double minus sign, (as in --), in the formula do? GBExcel T. Valko wrote: >Try this... > >=IF(BA24="Pay >slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"") > >> Hi, >> >[quoted text clipped - 32 lines] >> >> GBExcel -- Message posted via http://www.officekb.com
From: T. Valko on 16 Mar 2010 17:58 >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 -- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438(a)uwe> wrote in message news:a51c6959e40cf(a)uwe... > Yes! Yes! Yes! > > I'm so excited -- it really works! :-) > > Thank you. It was giving me a headache. > > Can I ask a further question; What does the double minus sign, (as in --), > in > the formula do? > > GBExcel > > T. Valko wrote: >>Try this... >> >>=IF(BA24="Pay >>slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"") >> >>> Hi, >>> >>[quoted text clipped - 32 lines] >>> >>> GBExcel > > -- > Message posted via http://www.officekb.com >
From: GBExcel via OfficeKB.com on 16 Mar 2010 19:45
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 |