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

 

[msohail]

At present the SUMIF is using the studid and not name, I want it to use both. I don't know how to combine both in the formula, would it be through AND as in the email or some other way??

 

[Aladin Akyurek]

On 2002-10-21 08:21, msohail wrote:
At present the SUMIF is using the studid and not name, I want it to use both. I don't know how to combine both in the formula, would it be through AND as in the email or some other way??

Try if you want to...

=IF(AND(B2<>B3,C2<>C3),SUMPRODUCT(($B$2:$B$40=B2)+0,($C$2:$C$40=C2)+0,$D$2:$D$40),"")
This message was edited by Aladin Akyurek on 2002-10-21 08:27

 

[WillR]

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

You may want to look at adding a column to your data to test if column B contains the Landlord or the Student.... i.e., youd get a result in col d of either 'Landlord' or 'Student'.

Then you could add another test to Aladin's formula such as

=IF((AND(C2<>C3,D2<>"Landlord",D3<>"Landlord"),SUMIF($C$2:$C$40,C2,$E$2:$E$40),""))

HTH

_________________
Will<img src=http://www.smilies.nl/dieren/pengy.gif>
Do NOT feed the Penguin!
This message was edited by WillR on 2002-10-21 08:30

 

[msohail]

Thanq Aladin,
your very useful formula has produced the following result:
col_B col_C(Stud ID) col_D(£) Formula
Smith 111222 100 blank
Smith 111222 50 blank1
Walker 111222 75 75

It correctly returns the value of £225 where the 75 is if the entry in B3 was also 'Smith', but as it's diff I would like it to return a value of 150 in blank1, leaving 'blank' blank. I think it just needs a little tweek somewhere. Mohammed

 

[Aladin Akyurek]

On 2002-10-21 10:26, msohail wrote:
Thanq Aladin,
your very useful formula has produced the following result:
col_B col_C(Stud ID) col_D(£) Formula
Smith 111222 100 blank
Smith 111222 50 blank1
Walker 111222 75 75

It correctly returns the value of £225 where the 75 is if the entry in B3 was also 'Smith', but as it's diff I would like it to return a value of 150 in blank1, leaving 'blank' blank. I think it just needs a little tweek somewhere. Mohammed

Let A1:C4 house the sample you provide above:

{"Name","ID","Amount";
"Smith",111222,100;
"Smith",111222,50;
"Walker",111222,75}

Create a unique list of pairs of IDs and Names in E1:F3 (using Advanced Filter, for example)...

{"Name","ID";
"Smith",111222;
"Walker",111222}

In G2 enter & copy down:

=SUMPRODUCT(($A$2:$A$4=E2)*($B$2:$B$4=F2),$C$2:$C$4)


Another method of computation would be to build a pivot table from your data...

 

[msohail]

I've explored the pivot table option but really only need a formula. Your formula was almost there. Is not possible to change the ending of the formula from ',""' to another sumif type statement or another IF formula?? Mohammed

 

[Aladin Akyurek]

On 2002-10-21 11:29, msohail wrote:
I've explored the pivot table option but really only need a formula. Your formula was almost there. Is not possible to change the ending of the formula from ',""' to another sumif type statement or another IF formula?? Mohammed

You insist on having a running total...

In D2 enter & copy down:

=IF((A2=A3)*(B2=B3),"",SUMPRODUCT(($A$2:$A$4=A2)*($B$2:$B$4=B2),$C$2:$C$4))

Addendum: See the figure...

Book3
	A	B	C	D	E	F	G
1	Name	ID	Amount	RunningTotal	Name	ID	Total
2	Smith	111222	100		Smith	111222	150
3	Smith	111222	50	150	Walker	111222	75
4	Walker	111222	75	75
5
Sheet1

This message was edited by Aladin Akyurek on 2002-10-21 12:19

 

[msohail]

Thanks so much Aladin. How did u get the summary data in columns E, F, G?? I would like to get the summary on a seperate sheet which would summarise like this:
Name ID Desc Date Total etc

As u have done in cells EFG but on a seperate worksheet. Is it possible for the formula to pick up absolute text values (for name and desc and date and ID fields)?? If i enter the same formula it summs the ID's. I probably need to change the 'sumproduct' part of the formula to 'text' ot 't' or something else which returns absolute values.

Mohammed

 

[Aladin Akyurek]

On 2002-10-22 05:10, msohail wrote:
Thanks so much Aladin. How did u get the summary data in columns E, F, G?? I would like to get the summary on a seperate sheet which would summarise like this:
Name ID Desc Date Total etc

As u have done in cells EFG but on a seperate worksheet. Is it possible for the formula to pick up absolute text values (for name and desc and date and ID fields)?? If i enter the same formula it summs the ID's. I probably need to change the 'sumproduct' part of the formula to 'text' ot 't' or something else which returns absolute values.

Mohammed,

The E:G bit was my initial proposal...

Let the data range A1:C4 be in Sheet1.

Name a worksheet Summary.

( 1.) Activate cell A1 in Summary.
( 2.) Activate Data|Filter|Advanced Filter.
( 3.) Check Copy to another location for Action.
( 4.) Enter Sheet1!$A$1:$B$4 in the List range box.
( 5.) Leave the Criteria range box empty.
( 6.) Enter Summary!$A$1 in the Copy to box.
( 7.) Check Unique records only.
( 8.) Click OK.

In C1 enter: Total [ just a label ]

In C2 enter & copy down:

=SUMPRODUCT((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$B$4=B2),Sheet1!$C$2:$C$4)

This is what I proposed initially instead of a running total in an additional column next to the data area. This time it is done in a different sheet instead of Sheet1.

I might add that, as an alternative approach, you can also build a pivot table from your original data...

See below how that looks...

aaCondSumRunningTotal msohail.xls
	A	B	C	D
3	SumofAmount	ID
4	Name	111222	GrandTotal
5	Smith	150	150
6	Walker	75	75
7	GrandTotal	225	225
Sheet3