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

Easy to give you the sumproduct formula but google for

SUMPRODUCT:EXCEL
Post back if you still can't get it.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

=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

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

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

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