# Combining SUM & VLOOKUP

#### msohail

##### Board Regular
Hi,
could u pls help with formula:
I would like the formula to look up in a column, say C (student ID), and for all the identical values return the SUM from column D(fees). Thanq

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
On 2002-10-10 07:19, msohail wrote:
Hi,
could u pls help with formula:
I would like the formula to look up in a column, say C (student ID), and for all the identical values return the SUM from column D(fees). Thanq

=SUMIF(C2:C40,E1,D2:D40)

where E1 houses a student ID of interest.

Thanks Aladin but this only works in part. It correctly returns the sum value in the first instance but then repeats the same on the second cell and so on for the number of cells containing the same ID in column C. I want it return a single sum for each unique stud ID and then move on to find the next stud ID and return the sum for that in the next cell down. If confused, could i pls email the s/sheet to someone 4 help? Mohammed

There a couple of other ways to get this information.

If you sort your data by student ID (ie so that all the entries for each student are next to each other), you can then goto Data|SubTotals and tick the fees box, this will give you a sub total below each student showing their total fees...however this means the subtotals are in amongst the data which might not be what you want.

You could also create a pivot table of your data with student id in the Rows section and Sum of Fees in the data section, this will give you a new table showing each student ID and the total fees they owe.

Thanks PQ but I really do need it to work as a formula as the worksheet containing the formula will be uploaded into the accounting system. Is there any other fields i can add to the original entry sheet such as a COUNTIF formula e.g. to facilitate the task??

On 2002-10-10 07:56, msohail wrote:
Thanks Aladin but this only works in part. It correctly returns the sum value in the first instance but then repeats the same on the second cell and so on for the number of cells containing the same ID in column C. I want it return a single sum for each unique stud ID and then move on to find the next stud ID and return the sum for that in the next cell down. If confused, could i pls email the s/sheet to someone 4 help? Mohammed

Try:

=IF(C2<>C3,SUMIF(\$C\$2:\$C\$40,C2,\$D\$2:\$D\$40),"")

Thanks All, I've tried the suggestions but no luck so far. Shall pick your brains again once i'm able to attach the s/sheet for u 2 c as u'll better understand my query. Thanks all the same for now.

On 2002-10-10 12:45, msohail wrote:
Thanks All, I've tried the suggestions but no luck so far. Shall pick your brains again once i'm able to attach the s/sheet for u 2 c as u'll better understand my query. Thanks all the same for now.

You need to tell/convey more than just "but no luck so far"... What do you get as result? Try to be a bit more specific?

I've tried your formula below and it did produce the desired result, thankyou so much, and everyone for their contributions.
=IF(C2<>C3,SUMIF(\$C\$2:\$C\$40,C2,\$D\$2:\$D\$40),"")

I now wish to extend the formula to say =IF (C2 AND B2.....) i.e. look for both the student ID (c2) and also their name (b2) and then summ where both these fields are identical. This added factor is required because a single ID can have 2 diff names, landlords name aswell as student name. I've tried this formula, variation of the above, but receive this error message (#NAME)

=IF(B2 AND C2<>B3 AND C3,SUMIF(\$B\$1:\$C\$23,B2 AND C2,\$E\$1:\$E\$23),"")

Could u pls help with how I might need to change the formula to accomodate the above. Many Thanks,

Mohammed

On 2002-10-21 07:53, msohail wrote:
I've tried your formula below and it did produce the desired result, thankyou so much, and everyone for their contributions.
=IF(C2<>C3,SUMIF(\$C\$2:\$C\$40,C2,\$D\$2:\$D\$40),"")

I now wish to extend the formula to say =IF (C2 AND B2.....) i.e. look for both the student ID (c2) and also their name (b2) and then summ where both these fields are identical. This added factor is required because a single ID can have 2 diff names, landlords name aswell as student name. I've tried this formula, variation of the above, but receive this error message (#NAME)

=IF(B2 AND C2<>B3 AND C3,SUMIF(\$B\$1:\$C\$23,B2 AND C2,\$E\$1:\$E\$23),"")

Could u pls help with how I might need to change the formula to accomodate the above. Many Thanks,

Mohammed

What is this story of a stud ID being associated with a landlord's name or the student's name? If that's indeed the case, testing for names would not be reliable. You could change the SUMIF formula to use the stud ID's instead of stud names.

Replies
15
Views
387
Replies
7
Views
199
Replies
1
Views
180
Replies
0
Views
136
Replies
1
Views
161

1,203,242
Messages
6,054,350
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back