| View previous topic :: View next topic |
| Author |
Message |
MJS123
Newbie
Joined: 22 Mar 2012
Posts: 1
|
 Posted: Thu Mar 22, 2012 2:28 am Post subject: Very easy formula.. Help |
 |
|
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 |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Thu Mar 22, 2012 2:54 am Post subject: Re: Very easy formula.. Help |
|
|
| 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 |
|
|
Mueller
Newbie
Joined: 26 Mar 2012
Posts: 2
Location: Bielefeld, Germany
|
| Posted: Mon Mar 26, 2012 10:16 am Post subject: data pilot |
|
|
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)
 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 
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 |
|
|
master-plan
General User
Joined: 29 Mar 2012
Posts: 8
|
| Posted: Thu Mar 29, 2012 9:48 am Post subject: |
|
|
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 |
|
|
|
