SUM IF SUB TOTAL Problem

[Andy in Edinburgh]

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

 

[Roger Govier]

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 Special>Values back over the data in
column B

Do the same thing with the ID's on Sheet2

 

[Peo Sjoblom]

=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]

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