Combining SUM & VLOOKUP

[msohail]

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

 

[Aladin Akyurek]

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.

 

[msohail]

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

 

[ThePencilQueen]

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.

 

[msohail]

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??

 

[Aladin Akyurek]

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),"")

 

[msohail]

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.

 

[Aladin Akyurek]

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?

 

[msohail]

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

 

[Aladin Akyurek]

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.