[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
glaste
Newbie

Joined: 14 Nov 2003
Posts: 2

 Posted: Fri Nov 14, 2003 8:01 am    Post subject: Conditionnal subtotal on openoffice calc 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

 Posted: Fri Nov 14, 2003 10:36 am    Post subject: Conditional Sum =SUMIF(B2:B8;"=catA";C2:C 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

 Posted: Fri Nov 14, 2003 10:44 am    Post subject: 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
Moderator

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

 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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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