| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM IF SUB TOTAL Problem
Hello
My problem is I have a list of data on one sheet that needs SUMMING and the answer to appear on sheet 2. The list on sheet 1 is a list of customers who owe money to a Financial Advisor. Column A amount due Column B the IFA reference ID. On sheet 2 is a list of the IFA's. I need to calculate how much each IFA is due to be paid from the list of customers. I've tried using a SUMIF formula and this works if there is only 1 customer and 1 IFA but I can't search the list and return a value due to that IFA for all the customers on the list. Below is a small sample of the data the full list is a thousand customers long and has about 300 IFA's. Thanks in advance Andy Sheet 1 IFA FEE CLIIFAID 0.67 3356000000 0.08 5056000000 119.3 5056000000 94.1 5056000000 102.96 5056000000 82.01 5056000000 77.17 5056000000 76.85 5056000000 65.22 5056000000 81.5 5056000000 25.84 100013004383 181.61 100013577710 37.28 100013577710 50.27 100825007503 47.85 100906000020 19.77 100906000020 Sheet 2 IFA ID TOTAL IFA DUE 3356000000 0 5056000000 0 100013004383 0 100013577710 0 100825007503 0 100906000020 0 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF SUB TOTAL Problem
=SUMIF(Sheet1!B:B,A2,Sheet1!A:A)
Why wouldn't the above work input in B2 (first cell to the right of the first IFA ID) and copy it down? -- Regards, Peo Sjoblom "Andy in Edinburgh" <Andy in wrote in message ... Hello My problem is I have a list of data on one sheet that needs SUMMING and the answer to appear on sheet 2. The list on sheet 1 is a list of customers who owe money to a Financial Advisor. Column A amount due Column B the IFA reference ID. On sheet 2 is a list of the IFA's. I need to calculate how much each IFA is due to be paid from the list of customers. I've tried using a SUMIF formula and this works if there is only 1 customer and 1 IFA but I can't search the list and return a value due to that IFA for all the customers on the list. Below is a small sample of the data the full list is a thousand customers long and has about 300 IFA's. Thanks in advance Andy Sheet 1 IFA FEE CLIIFAID 0.67 3356000000 0.08 5056000000 119.3 5056000000 94.1 5056000000 102.96 5056000000 82.01 5056000000 77.17 5056000000 76.85 5056000000 65.22 5056000000 81.5 5056000000 25.84 100013004383 181.61 100013577710 37.28 100013577710 50.27 100825007503 47.85 100906000020 19.77 100906000020 Sheet 2 IFA ID TOTAL IFA DUE 3356000000 0 5056000000 0 100013004383 0 100013577710 0 100825007503 0 100906000020 0 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF SUB TOTAL Problem
Thank you so much I was very close with many of my attempts but I've been
scratching my head about this all day. Have a great day Andy "Roger Govier" wrote: hi Andy On sheet2 B2 =SUMIF(Sheet1!B:B,A2,Sheet1!A:A) should give you the correct result. If it is not, then you need to check and be sure that the ID's on both sheets match i.e. there are no extraneous spaces. Was the data entered, or copied from elsewhere? In a spare column on Sheet1 enter =SUBSTITUTE(SUBSTITUTE(B2," ",""),Char(160),"") and copy down as far as required. Then copy that block of data and Paste SpecialValues back over the data in column B Do the same thing with the ID's on Sheet2 -- Regards Roger Govier "Andy in Edinburgh" <Andy in wrote in message ... Hello My problem is I have a list of data on one sheet that needs SUMMING and the answer to appear on sheet 2. The list on sheet 1 is a list of customers who owe money to a Financial Advisor. Column A amount due Column B the IFA reference ID. On sheet 2 is a list of the IFA's. I need to calculate how much each IFA is due to be paid from the list of customers. I've tried using a SUMIF formula and this works if there is only 1 customer and 1 IFA but I can't search the list and return a value due to that IFA for all the customers on the list. Below is a small sample of the data the full list is a thousand customers long and has about 300 IFA's. Thanks in advance Andy Sheet 1 IFA FEE CLIIFAID 0.67 3356000000 0.08 5056000000 119.3 5056000000 94.1 5056000000 102.96 5056000000 82.01 5056000000 77.17 5056000000 76.85 5056000000 65.22 5056000000 81.5 5056000000 25.84 100013004383 181.61 100013577710 37.28 100013577710 50.27 100825007503 47.85 100906000020 19.77 100906000020 Sheet 2 IFA ID TOTAL IFA DUE 3356000000 0 5056000000 0 100013004383 0 100013577710 0 100825007503 0 100906000020 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counta problem - Wrong total | Excel Discussion (Misc queries) | |||
| Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
| Problem with summing formula updating Total... | Excel Discussion (Misc queries) | |||
| Pivot table from CSV data, problem with percentage of total. | Excel Worksheet Functions | |||
| what is the problem, when value comes up in the total column | Excel Worksheet Functions | |||
Linear Mode
