If its simple, can someone explain to me how to do the following but if its complicated then just tell me so!
I want to do a table with values and beside each value give it a numeric code between, say 1 & 10.
At the bottom I then want to do a summary of the total 1's, total 2's etc.
eg. car costs might be a code 1, Over the month I might have fuel, tax, servicing expenditure that are all car related.
How do I then get a summary of the total car expenditure at the bottom?
Is this straightforward?
|
Should be straightforward - try using the SUMIF function.
Say you've got 30 lines of data, your codes are in column A and the numbers you want to add up by category in column B, and you want to total all the numbers in code 1, what you'd need to put in is:
=SUMIF(A1:A30,1,B1:B30)
What this tells Excel to do is go down column A from row 1 to row 30, looking for the value 1, and add up all the corresponding entries in column B.
You can then repeat the same function for your other codes.
|
|
Cheers for that Paul, will give it a try.
|
OK thats working well, taking it one stage further, how do I relate this same formula to a different worksheet?
So if all the data is in worksheet 1 but I want the formula entry to be in worksheet 2 for instance, how so I relate this formula?
|
Simple enough - using the previous example, re-enter it as follows in the sheet where you want the results:
=SUMIF('Sheet1'!A1:A30,1,'Sheet1'!B1:B30)
...where Sheet1 is the name of the worksheet where the data is. Whatever the sheet's name is must be inside the quotes.
Last edited by: paulb on Tue 25 Jan 11 at 22:28
|
Excellent Paul, that gives me enough info to go ahead and do my worksheet. Just one last question, how do I copy a formula from one box to another without changing the corresponding entries?
So for example if I have the following formula in box K2
=SUMIF(G2:G60,1,D2:D60)
and I copy that formula into K3, then each of the numbers will go up by 1, if that makes sense? How do I keep them the same as those figures are the range ie I am looking to copy and paste this formula into all the boxes and then just go back and edit the "1" to "2", "3" etc?
Does that make sense?
|
Just put $ signs in front of the row and column references - like this:
=SUMIF($G$2:$G$60,1,$D$2:$D$60)
The $s lock the cell references, so it will pick up the same range each time and you don't then get the cell reference iteration you had there when you copy and paste the formula.
|
Thanks, that gives me enough to go on so that will keep me busy for the next few nights so you now have some time off!!! :)
Many thanks again.... until the next question!
|
|
Bear in mind that in Excel once you've got two rows of your formula entered you can fill however many more rows you need it in using 'smart fill' or whatever it's called these days. Highlight the cells with the formula in, hover the mouse pointer over the bottom right corner of the highlighted area and a cross should appear. Click on this with the mouse pointer and drag down for however many rows up formula you need
|
Cheers guys for your help, spreadsheet now fully up and running.
Next question, anyone any formulas that actually create money rather than just tell you where it has all gone???? :)
|
|
I've tried using the RND function to generate next week's lottery numbers but no luck yet.
|
|
I did exactly that the 1st week of the lottery all thoise years ago. I won. A whole £10. It has never worked since!
|
Excel spreadsheet formula - paulb
