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

Very easy formula.. Help

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


Joined: 22 Mar 2012
Posts: 1

PostPosted: Thu Mar 22, 2012 2:28 am    Post subject: Very easy formula.. Help Reply with quote

I'm sure this will be super easy for most of you..

I'm adding up all my receipts for my business..

In column A, I have text, in column B I have a value...

So for example, column A might say "Diesel, Tools, or Materials" Lets just say it says "X"

And colums B has the amount.

I want to count the sum of the values in coloumn B, IF the corosponding row A has "X"
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Thu Mar 22, 2012 2:54 am    Post subject: Re: Very easy formula.. Help Reply with quote

MJS123 wrote:
I want to count the sum of the values in coloumn B, IF the corosponding row A has "X"

If you want to count then use something like...
Code:
=COUNTIF($A$1:$A$100;"X")

If you want to sum then it would be something like...
Code:
=SUMIF($A$1:$A$500;"X";$B$1:$B$500)

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Mueller
Newbie
Newbie


Joined: 26 Mar 2012
Posts: 2
Location: Bielefeld, Germany

PostPosted: Mon Mar 26, 2012 10:16 am    Post subject: data pilot Reply with quote

hey mate,
kens answer was right to the point, of course, but i would like to add something:
i assume you want to calculate the sum and(or count for EVERY category you have, and doing so with the countif/sumif functions may be time consuming.
maybe you want to take a look at the data pilot (aka pivot table), it isnt all that hard.

(forgive my crappy english, i am not a native speaker. if anything is unclear and someone else reads this, pls feel free to correct me. i use the german calc, so the translations of manus etc. will be fuzzy)

1) select a single cell in your list (one that actually has text or numbers in it, but only ONE)
2) oben the "data" menue, go to data-pilot and click the first command in the sub-menue (something like "create")
3) in the window leave "current selection" checked and just click "okay"
4) in the next window you have some large fields on the left and a list with your column-headers on the right. drag and drop the header of your category-column (column a in your example) to the leftest of the white fields (probably called something like row-field. its the only box that is vertical)
5) drag and trop the column with the numbers (column b) into the largest of the white fields (called data-field)
6) double-click on the grey box you just draged into the data-field (called sum-<your header>)
7) choose a function (like count or average)
Cool click okay
9) click okay again

under your list you will have now results for all your categories.

play around a little with the data-pilot. it is a great tool. i suggest it for you coz u say you have your own business, and i assume you will sooner or later need more analysis which the pilot could provide. good idea to get started with it now Wink
there is a good documentation of the pilot in the free calc manuals, if you are interested in more ...

hope that helps
Back to top
View user's profile Send private message
master-plan
General User
General User


Joined: 29 Mar 2012
Posts: 8

PostPosted: Thu Mar 29, 2012 9:48 am    Post subject: Reply with quote

Agree with Mueller... Pivot tables might be the way to go on this one especially if you want to further analyze the data: I just did something very similar myself a couple of days back. It's relatively easy to export data from Base to Calc.

If you need additional help let us know and we can provide additional step by step procedures.

Cheers.
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