=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