Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate totals
Hi, Any help would be greatly appreciated.
Column A contains different divisions (there will several lines for each divison). If Col C1 contains a 1 then the amount in Col B1 is to be calculated for each like division in Col A Example: First line Col A (1), B ($-7954.07), C (1) Second line A (1), B ($1000.00), C (blank) Third line A(1), B ($50.00), C (1), In Col D a list of each division Col E for the example above would be totals from line 1 and line 3 $-7904.07 which would go in Col E (Col D is a list of all the different divisions) And then so forth for each division. Thanks Donna |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate totals
Donna,
With headings in row1, insert this formula in E2, adjust ranges in column A:C (same numeber of rows in all columns), and copy the formula down to all divisions. =SUMPRODUCT(--($A$2:$A$10=D2),--($C$2:$C$10=1),$B$2:$B$10) Regards, Per "Donna" skrev i meddelelsen ... Hi, Any help would be greatly appreciated. Column A contains different divisions (there will several lines for each divison). If Col C1 contains a 1 then the amount in Col B1 is to be calculated for each like division in Col A Example: First line Col A (1), B ($-7954.07), C (1) Second line A (1), B ($1000.00), C (blank) Third line A(1), B ($50.00), C (1), In Col D a list of each division Col E for the example above would be totals from line 1 and line 3 $-7904.07 which would go in Col E (Col D is a list of all the different divisions) And then so forth for each division. Thanks Donna |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate totals
Donna,
Using your example: Column A Column B Column C Column D Column E 1 $-7,954.07 1 Name 1 $ 1,000.00 Name 1 $ 50.00 1 Name $-7,940.07 The formula in Cell E3 would be: "=SUMIF(C1:C3,"=1",B1:B3)" What this formula does - look in cells C1, C2, and C3 and see if it is equal to 1. If it is equal to 1, then adds the corresponding value in B together. If each compnay name has its own number, just change the 1 to what ever number matches the company. hth "Donna" wrote: Hi, Any help would be greatly appreciated. Column A contains different divisions (there will several lines for each divison). If Col C1 contains a 1 then the amount in Col B1 is to be calculated for each like division in Col A Example: First line Col A (1), B ($-7954.07), C (1) Second line A (1), B ($1000.00), C (blank) Third line A(1), B ($50.00), C (1), In Col D a list of each division Col E for the example above would be totals from line 1 and line 3 $-7904.07 which would go in Col E (Col D is a list of all the different divisions) And then so forth for each division. Thanks Donna |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate totals
Hi,
I tried that and it worked, however I don't think I explained it very well. I am using this in a macro. The number of lines in col A is random, there may by 5 lines for Div 1 and 15 lines for div 2. What I would like to do is if col C (meets criteria of a 1), then it would look in column A and all the lines for div 1 that have a 1 in col C it would total in col E with the corresponding div number in D. Then it would look in Col C again and do the same thing for div. 2. Sorry I was so confusing. Donna "FloMM2" wrote: Donna, Using your example: Col A Col B Col C Col D Col E Division Amount Meets Total Writeoff Criteria Div 1 $-7,954.07 1 1 $-7,940.07 1 $ 1,000.00 2 $5.00 1 $ 50.00 1 3 0 2 $ 5.00 1 3 $25.00 The formula in Cell E3 would be: "=SUMIF(C1:C3,"=1",B1:B3)" What this formula does - look in cells C1, C2, and C3 and see if it is equal to 1. If it is equal to 1, then adds the corresponding value in B together. If each compnay name has its own number, just change the 1 to what ever number matches the company. hth "Donna" wrote: Hi, Any help would be greatly appreciated. Column A contains different divisions (there will several lines for each divison). If Col C1 contains a 1 then the amount in Col B1 is to be calculated for each like division in Col A Example: First line Col A (1), B ($-7954.07), C (1) Second line A (1), B ($1000.00), C (blank) Third line A(1), B ($50.00), C (1), In Col D a list of each division Col E for the example above would be totals from line 1 and line 3 $-7904.07 which would go in Col E (Col D is a list of all the different divisions) And then so forth for each division. Thanks Donna |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate totals for a range of names | Excel Worksheet Functions | |||
Calculate totals by fields | Excel Discussion (Misc queries) | |||
Calculate totals | Excel Discussion (Misc queries) | |||
calculate totals | Excel Discussion (Misc queries) | |||
formulae to calculate sub totals | Excel Programming |