I would like to subtotal two ways. I have three colums, see examples.

Req. Item Qty Dept
2XL Gowns 10 L&D
2XL Gowns 11 UPC2
2XL Gowns 15 L&D

I want it to sum the about for each dept and then count that L&D had 25 and
they ordered twice and then 11 for UPC2 and got it once, can I do that using
subtotal.

Thanks

On Mon, 2 Mar 2009 17:31:02 -0800, Anthony
wrote:

I would like to subtotal two ways. I have three colums, see examples.

Req. Item Qty Dept
2XL Gowns 10 L&D
2XL Gowns 11 UPC2
2XL Gowns 15 L&D

I want it to sum the about for each dept and then count that L&D had 25 and
they ordered twice and then 11 for UPC2 and got it once, can I do that using
subtotal.

Thanks

Try using a Pivot Table.

I dragged Dept to Rows area
Req. Item to Value (or Data) area
Qty to Value area

I produced this, but you can make it prettier:

Values
Row Labels Count of Req. Item Sum of Qty
L&D 2 25
UPC2 1 11
Grand Total 3 36
--ron

Hi,

To reach the layout where sum and count are in separate columns you will
need to drag the Data button to the Column area (2003).

If you want to do this with functions:

=SUMIF(C$2:C$10,F1,B2:B10)
and
=COUNTIF(C$2:C$10,F1)

Where you list the different departments from F1 down. The enter the first
formula in G1 and the second one in H1 and copy them down.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ron Rosenfeld" wrote:

On Mon, 2 Mar 2009 17:31:02 -0800, Anthony
wrote:

I would like to subtotal two ways. I have three colums, see examples.

Req. Item Qty Dept
2XL Gowns 10 L&D
2XL Gowns 11 UPC2
2XL Gowns 15 L&D

I want it to sum the about for each dept and then count that L&D had 25 and
they ordered twice and then 11 for UPC2 and got it once, can I do that using
subtotal.

Thanks

Try using a Pivot Table.

I dragged Dept to Rows area
Req. Item to Value (or Data) area
Qty to Value area

I produced this, but you can make it prettier:

Values
Row Labels Count of Req. Item Sum of Qty
L&D 2 25
UPC2 1 11
Grand Total 3 36
--ron

On Mon, 2 Mar 2009 19:38:00 -0800, Shane Devenshire
wrote:

To reach the layout where sum and count are in separate columns you will
need to drag the Data button to the Column area (2003).

I have 2007 and don't have the particulars for earlier version. Thanks for
pointing that out.
--ron

Thanks for your help, the pivot table worked great. I must have mad a brain
block to not think of that before, I use pivots alot. Thanks again,

Anthony

"Ron Rosenfeld" wrote:

On Mon, 2 Mar 2009 19:38:00 -0800, Shane Devenshire
wrote:

To reach the layout where sum and count are in separate columns you will
need to drag the Data button to the Column area (2003).

I have 2007 and don't have the particulars for earlier version. Thanks for
pointing that out.
--ron

On Tue, 3 Mar 2009 09:46:02 -0800, Anthony
wrote:

Thanks for your help, the pivot table worked great. I must have mad a brain
block to not think of that before, I use pivots alot. Thanks again,

You're welcome. Glad to help. Thanks for the feedback.
--ron