keeping subtotals accurate to reflect sorting...
 

Hi, not sure how to present my question briefly....

I've created an inventory sheet for our restaurants with the following
columns: category, description, vendor, item #, price, etc etc etc.

At the bottom of the 250 some odd line items I have everything
subtotaled by category (all beverages, dairy, spices, produce, etc)
and created those subtotals by typing "=sum(r4:r21,u4:21)" as
beverages are in rows 4-21 and inventory calculation results are found
in columns R and U. and I repleated this for all categories...

this is fine until people start sorting. when sorting by item number
for example, beverages are no longer rows 4-21, but maybe 4,
17,22-28,99,100-102 or whatever. but my totals at the bottom don't
reflect that and still say beverages "=sum(r4:r21,u4:21)"

how can I get my subtotals at the bottom to "stick with" the beverages
categories? I feel like the solution is right under my nose...

thanks in advance.

keeping subtotals accurate to reflect sorting...
 

Try using the SUMIF function.

...........A..........B
1.....Bev.........5
2.....Pro..........3
3.....Bev.........2
4.....Sp...........3
5.....Bev.........2

=SUMIF(A1:A5,"Bev",B1:B5)

--
Biff
Microsoft Excel MVP


"Amanda Fekety" wrote in message
...
Hi, not sure how to present my question briefly....

I've created an inventory sheet for our restaurants with the following
columns: category, description, vendor, item #, price, etc etc etc.

At the bottom of the 250 some odd line items I have everything
subtotaled by category (all beverages, dairy, spices, produce, etc)
and created those subtotals by typing "=sum(r4:r21,u4:21)" as
beverages are in rows 4-21 and inventory calculation results are found
in columns R and U. and I repleated this for all categories...

this is fine until people start sorting. when sorting by item number
for example, beverages are no longer rows 4-21, but maybe 4,
17,22-28,99,100-102 or whatever. but my totals at the bottom don't
reflect that and still say beverages "=sum(r4:r21,u4:21)"

how can I get my subtotals at the bottom to "stick with" the beverages
categories? I feel like the solution is right under my nose...

thanks in advance.