Combining SUM & VLOOKUP

msohail

Board Regular
Joined
Oct 9, 2002
Messages
120
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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??
 
Upvote 0
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),"")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Aladin,
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
 
Upvote 0
On 2002-10-21 07:53, msohail wrote:
Aladin,
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.
 
Upvote 0

Forum statistics

Threads
1,224,396
Messages
6,178,394
Members
452,844
Latest member
Shebl

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top