Sum with multiple criteria

[Blake_Allen]

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

 

[Don Guillett]

Easy to give you the sumproduct formula but google for

SUMPRODUCT:EXCEL
Post back if you still can't get it.

 

[Bernard Liengme]

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

 

[Dave Peterson]

=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 here:
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/assistance/2002/articles/xlconPT101.aspx

 

[Dave Peterson]

ps. xl2007 has a new =sumifs() function (if you're using that version).

 

[Ashish Mathur]

Hi,

Have you tried playing around with pivot tables.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

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