View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default 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