Using IF

[nfphilpot]

I am looking for a formula that looks up the value of a cell if another cell contains certain text. ie.



.....A.......B........C.......D
1 John....£30....£55....£90
2 Tim......£40
3 Rosie....£30
4 John.....£25
5 Tim......£50

(excuse my use of '..' to get a correctish lay out...so novice i know

The relationship between columns A and B is 'A' ie John saved £30 and at a later date saved £25 (cell 4B)

So im looking for a formula that adds up all that each individual has saved.

Something like

=IF(A1:A5) equals 'John' then add all the values in column 'B' (in this case B1 and B4) and display the total in another Cell, say C1

=IF(A1:A5) equals 'Tim' then add all the values in column 'B' (in this case B2 and B5) and display the total in another Cell, say D1

Is there a formula that is cunning enough to add up all John, Tim and Rosie's individual totals

please imagine a lot of names and a lot of amounts.

Regards

Nic

[nfphilpot]

here is the file in question, note the laborious and basic formulas

the #VALUE! is cos the older version cannot support more than 30 'arguments'

[Kimberly]

=SUMIF(A1:A5,"john",B1:B5)

[Kimberly]

or better yet, enter a unique list of the names in Col J for example, and put this in K1

=SUMIF($A$1:$A$5,J1,$B$1:$B$5)
and copy down for all the names

[nfphilpot]

Originally Posted by Kimberly

or better yet, enter a unique list of the names in Col J for example, and put this in K1

=SUMIF($A$1:$A$5,J1,$B$1:$B$5)
and copy down for all the names

Would you mind giving me a quick example of this...cant figure exactly what you mean...

...thanks to the above formula, works a treat!

[Kimberly]

See attached.

[nfphilpot]

Originally Posted by Kimberly

See attached.

Perfect...Many thanks