|
Sum with multiple criteria
I am looking for a formula to retreive the sum within a column with multiple
criteria. I would like to be able to look totals for each day per shift. Totals by day per shift would be great if possiable? I am struggling with a formula that looks at date (A) and also a shift (B) and sum all of the cells in (C) that have the correct shift by date. Example: column A column B column C 1 12/20/08 1st shift 10 2 12/20/08 1st shift 20 3 12/20/08 2nd shift 5 4 12/20/08 1st shift 10 5 12/21/08 3rd shift 10 6 12/21/08 1st shift 10 1st shift should total 40 for 12/20/08 2nd shift should total 5 for 12/20/08 3rd shift should total 0 for 12/20/08 etc... If it matters this spread sheet will have approx 30000 rows? It will be for a whole years data. Thanks! Blake_Allen |
Sum with multiple criteria
For more details on SUMPRODUCT
Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Blake_Allen" wrote in message ... I am looking for a formula to retreive the sum within a column with multiple criteria. I would like to be able to look totals for each day per shift. Totals by day per shift would be great if possiable? I am struggling with a formula that looks at date (A) and also a shift (B) and sum all of the cells in (C) that have the correct shift by date. Example: column A column B column C 1 12/20/08 1st shift 10 2 12/20/08 1st shift 20 3 12/20/08 2nd shift 5 4 12/20/08 1st shift 10 5 12/21/08 3rd shift 10 6 12/21/08 1st shift 10 1st shift should total 40 for 12/20/08 2nd shift should total 5 for 12/20/08 3rd shift should total 0 for 12/20/08 etc... If it matters this spread sheet will have approx 30000 rows? It will be for a whole years data. Thanks! Blake_Allen |
Sum with multiple criteria
=sumproduct(--(a1:a10=date(2008,12,20)),--(b1:b10="1st shift"),c1:c10)
Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html =============== But this looks like you'd be able to use a pivottable and get a nice summary really quickly. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Blake_Allen wrote: I am looking for a formula to retreive the sum within a column with multiple criteria. I would like to be able to look totals for each day per shift. Totals by day per shift would be great if possiable? I am struggling with a formula that looks at date (A) and also a shift (B) and sum all of the cells in (C) that have the correct shift by date. Example: column A column B column C 1 12/20/08 1st shift 10 2 12/20/08 1st shift 20 3 12/20/08 2nd shift 5 4 12/20/08 1st shift 10 5 12/21/08 3rd shift 10 6 12/21/08 1st shift 10 1st shift should total 40 for 12/20/08 2nd shift should total 5 for 12/20/08 3rd shift should total 0 for 12/20/08 etc... If it matters this spread sheet will have approx 30000 rows? It will be for a whole years data. Thanks! Blake_Allen -- Dave Peterson |
Sum with multiple criteria
ps. xl2007 has a new =sumifs() function (if you're using that version).
Dave Peterson wrote: =sumproduct(--(a1:a10=date(2008,12,20)),--(b1:b10="1st shift"),c1:c10) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html =============== But this looks like you'd be able to use a pivottable and get a nice summary really quickly. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Blake_Allen wrote: I am looking for a formula to retreive the sum within a column with multiple criteria. I would like to be able to look totals for each day per shift. Totals by day per shift would be great if possiable? I am struggling with a formula that looks at date (A) and also a shift (B) and sum all of the cells in (C) that have the correct shift by date. Example: column A column B column C 1 12/20/08 1st shift 10 2 12/20/08 1st shift 20 3 12/20/08 2nd shift 5 4 12/20/08 1st shift 10 5 12/21/08 3rd shift 10 6 12/21/08 1st shift 10 1st shift should total 40 for 12/20/08 2nd shift should total 5 for 12/20/08 3rd shift should total 0 for 12/20/08 etc... If it matters this spread sheet will have approx 30000 rows? It will be for a whole years data. Thanks! Blake_Allen -- Dave Peterson -- Dave Peterson |
Sum with multiple criteria
Hi,
Have you tried playing around with pivot tables. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Blake_Allen" wrote in message ... I am looking for a formula to retreive the sum within a column with multiple criteria. I would like to be able to look totals for each day per shift. Totals by day per shift would be great if possiable? I am struggling with a formula that looks at date (A) and also a shift (B) and sum all of the cells in (C) that have the correct shift by date. Example: column A column B column C 1 12/20/08 1st shift 10 2 12/20/08 1st shift 20 3 12/20/08 2nd shift 5 4 12/20/08 1st shift 10 5 12/21/08 3rd shift 10 6 12/21/08 1st shift 10 1st shift should total 40 for 12/20/08 2nd shift should total 5 for 12/20/08 3rd shift should total 0 for 12/20/08 etc... If it matters this spread sheet will have approx 30000 rows? It will be for a whole years data. Thanks! Blake_Allen |
Sum with multiple criteria
On Sat, 20 Dec 2008 12:54:00 -0800, Blake_Allen
wrote: I am looking for a formula to retreive the sum within a column with multiple criteria. I would like to be able to look totals for each day per shift. Totals by day per shift would be great if possiable? I am struggling with a formula that looks at date (A) and also a shift (B) and sum all of the cells in (C) that have the correct shift by date. Example: column A column B column C 1 12/20/08 1st shift 10 2 12/20/08 1st shift 20 3 12/20/08 2nd shift 5 4 12/20/08 1st shift 10 5 12/21/08 3rd shift 10 6 12/21/08 1st shift 10 1st shift should total 40 for 12/20/08 2nd shift should total 5 for 12/20/08 3rd shift should total 0 for 12/20/08 etc... If it matters this spread sheet will have approx 30000 rows? It will be for a whole years data. Thanks! Blake_Allen A Pivot Table can easily generate a report like what you describe: Totals per Shift Date 1st 2nd 3rd Date Totals 12/20/2008 40 5 45 12/21/2008 10 10 20 Shift Totals 50 5 10 65 Give your data a header row: (e.g. Dates Shifts Count Insert/Pivot Table Drag Dates to Rows Area Shifts to Columns Area Count to Data (or Values) Area Format to taste --ron |
| All times are GMT +1. The time now is 06:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com