Conditionnal subtotal on openoffice calc

glaste · Newbie Joined: 14 Nov 2003 Posts: 2

Hi,

I would like to do subtotal of numbers regarding their category.

For instance :

___A_____ B_____ C______ D
__1_____catA____12
__2_____catB____14
__3_____catA_____5
__4_____catC____10
__5_____catC____10
__6_____catD_____8
__7_____catA_____2
__. ... ...
__. ... ...
__30_________Total catA____19
__31_________Total catB____14

My aim is to define a cell that shows the sum of the B column BUT only
with numbers that are "catA" (that will be 12+5+2=19)

Same for another cell that shows the sum of the "cat B" numbers (that
will be 14),
Same for "catC" and "catD" column. (that will be 20 for catC and 8 for
catD)

I will be able to add values or change values in column A and B so my
subtotal categories should be allways correct.

How can I do that in a simple way?

Thanks for your help,

Glaste

mathewgrimm · General User Joined: 07 Nov 2003 Posts: 6

=SUMIF(B2:B8;"=catA";C2:C Cool

In general,
=Sumif(range_to_be_evaluated_by_criteria, criteria, range_to_be_summed)

Hope this helps,
Mathew

mathewgrimm · General User Joined: 07 Nov 2003 Posts: 6

Sorry, should have disabled smilies in the previous post. Nice looking formula, though.

=SUMIF(B2:B8;"=catA";C2:C8)

In general,
=Sumif(range_to_be_evaluated_by_criteria, criteria, range_to_be_summed)

Hope this helps,
Mathew

dfrench · Posted: Fri Nov 14, 2003 1:03 pm Post subject:

A more general solution to this type of problem is to use DataPilot to create pivot tables then, as you introduce more categories, you do not have to add formulas.