Subtotal Function

[Anthony]

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

 

[Ron Rosenfeld]

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

 

[Shane Devenshire]

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.

 

[Ron Rosenfeld]

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

 

[Anthony]

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]

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