OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Conditionnal subtotal on openoffice calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
glaste
Newbie
Newbie


Joined: 14 Nov 2003
Posts: 2

PostPosted: Fri Nov 14, 2003 8:01 am    Post subject: Conditionnal subtotal on openoffice calc Reply with quote

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
Back to top
View user's profile Send private message
mathewgrimm
General User
General User


Joined: 07 Nov 2003
Posts: 6

PostPosted: Fri Nov 14, 2003 10:36 am    Post subject: Conditional Sum Reply with quote

=SUMIF(B2:B8;"=catA";C2:CCool

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

Hope this helps,
Mathew
Back to top
View user's profile Send private message
mathewgrimm
General User
General User


Joined: 07 Nov 2003
Posts: 6

PostPosted: Fri Nov 14, 2003 10:44 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Fri Nov 14, 2003 1:03 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group