Home |
Search |
Today's Posts |
#1
|
|||
|
|||
total a colum using data from another column
In column B I have numbers 1 thou 10. In column C I have different dollar
amounts I want a total of all the ones a total of all the twos etc in separate total cells e.g. 1 is the code for all the money spent on fuel this month. 2 is the code for all the money spent on office supplies etc |
#2
|
|||
|
|||
Say in D1 to D10 you enter the numbers 1 to 10.
Then in E1, enter this formula: =SUMIF(B:B,D1,C:C) And copy down to E10. Column E shows the total of each number in Column D. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Duffey" wrote in message ... In column B I have numbers 1 thou 10. In column C I have different dollar amounts I want a total of all the ones a total of all the twos etc in separate total cells e.g. 1 is the code for all the money spent on fuel this month. 2 is the code for all the money spent on office supplies etc |
#3
|
|||
|
|||
Hi,
To count all expense code 1, use =SUMIF(B:B,1,C:C) where column B has the codes for expenses and column C has the dollar value of expenses. Change the formula to suit other codes. Regards Govind. Duffey wrote: In column B I have numbers 1 thou 10. In column C I have different dollar amounts I want a total of all the ones a total of all the twos etc in separate total cells e.g. 1 is the code for all the money spent on fuel this month. 2 is the code for all the money spent on office supplies etc |
#4
|
|||
|
|||
Hi,
Assuming your worksheet is set up like this. Use the sumif formula Code Amount (col A) (Col B) 1 100 2 200 1 300 2 400 3 500 1 600 2 700 1 SUMIF($A$3:$A$9,"1",$B$3:$B$9) 2 SUMIF($A$3:$A$9,"2",$B$3:$B$9) 3 SUMIF($A$3:$A$9,"3",$B$3:$B$9) Regards, Ashish Mathur "Duffey" wrote: In column B I have numbers 1 thou 10. In column C I have different dollar amounts I want a total of all the ones a total of all the twos etc in separate total cells e.g. 1 is the code for all the money spent on fuel this month. 2 is the code for all the money spent on office supplies etc |
#5
|
|||
|
|||
This is exactly what I want to do but where would I put this formula? I need
separate totals at bottom of page e.g. office 1 15.00 fuel 2 20.00 tools 3 10.00 office 1 5.00 misc 4 50.00 fuel 2 10.00 Total = 20.00 (which is all the code ones totaled for office supplies) Total = 30.00 (which is all the code twos totaled for fuel) So would I have several cells with the different totals Duffey "Ashish Mathur" wrote: Hi, Assuming your worksheet is set up like this. Use the sumif formula Code Amount (col A) (Col B) 1 100 2 200 1 300 2 400 3 500 1 600 2 700 1 SUMIF($A$3:$A$9,"1",$B$3:$B$9) 2 SUMIF($A$3:$A$9,"2",$B$3:$B$9) 3 SUMIF($A$3:$A$9,"3",$B$3:$B$9) Regards, Ashish Mathur "Duffey" wrote: In column B I have numbers 1 thou 10. In column C I have different dollar amounts I want a total of all the ones a total of all the twos etc in separate total cells e.g. 1 is the code for all the money spent on fuel this month. 2 is the code for all the money spent on office supplies etc |
#6
|
|||
|
|||
use subtotals or pivot tables. see help in both
you need headings and you sort accoridng to first item and then use data-subtotals --------------------- Duffey wrote in message ... This is exactly what I want to do but where would I put this formula? I need separate totals at bottom of page e.g. office 1 15.00 fuel 2 20.00 tools 3 10.00 office 1 5.00 misc 4 50.00 fuel 2 10.00 Total = 20.00 (which is all the code ones totaled for office supplies) Total = 30.00 (which is all the code twos totaled for fuel) So would I have several cells with the different totals Duffey "Ashish Mathur" wrote: Hi, Assuming your worksheet is set up like this. Use the sumif formula Code Amount (col A) (Col B) 1 100 2 200 1 300 2 400 3 500 1 600 2 700 1 SUMIF($A$3:$A$9,"1",$B$3:$B$9) 2 SUMIF($A$3:$A$9,"2",$B$3:$B$9) 3 SUMIF($A$3:$A$9,"3",$B$3:$B$9) Regards, Ashish Mathur "Duffey" wrote: In column B I have numbers 1 thou 10. In column C I have different dollar amounts I want a total of all the ones a total of all the twos etc in separate total cells e.g. 1 is the code for all the money spent on fuel this month. 2 is the code for all the money spent on office supplies etc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
filling a forumla down a column from data across a row | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |