vlookup sum?

[Cheeks1969]

Hi, I have three columns that have amounts on the top row. Below that row are the names of people in 22 rows, for each row it;s the person who earned 60 in the first column, a person who earned 40 in the second, and a person who earned 20 in the third. Elsewhere, I have all the names listed once and I want to have their total earnings next to their names, calculated by a formula.
So for name X in the first column, I want to count them and multiply by 60, then add to the number of times that name shows up in the second column multiplied by 40, then number of times in the third column multiplied by 20. The sum of these would show up in the cell with my formula next to the person with name X. Repeat for other people.

Thanks for any help!

 

[RobP]

Hi,

Which version of Excel are you using, and can I ask, does each column only have unique names, or can they be repeated (ie. can someone earn 60 points twice or three times also ?)

cheers
Rob

 

[Cheeks1969]

Hi,

Which version of Excel are you using, and can I ask, does each column only have unique names, or can they be repeated (ie. can someone earn 60 points twice or three times also ?)

cheers
Rob

Microsoft 365 version. Each column can have a name more than one time. For example, person X might be in the first column 3 times, in the second column 0 times and in the third column 2 times. For person Y it might be 0, 1, 4. etc etc Thank you !

 

[RobP]

ok, try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		60	40	20
2		Bill		Bill					Bill	220
3		Steve	Phill						Steve	60
4		Trevor							Trevor	60
5		Iain	Tony						Iain	60
6		Jeff		Parker					Jeff	60
7									Phill	40
8			Bill						Tony	40
9			Bill						Rob	40
10		Bill							Parker	20
11									Jim	20
12									Keith	60
13									Bob	60
14
15		Keith
16
17
18			Rob
19
20
21
22		Bob		Jim
23
24
Sheet1

Cell Formulas
Range	Formula
J2:J13	J2	=COUNTIF(B$2:B$22,I2)*B$1+COUNTIF(C$2:C$22,I2)*C$1+COUNTIF(D$2:D$22,I2)*D$1

 

[Joe4]

Microsoft 365 version

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cheeks1969]

Thank you RobP, that is the solution! Joe, I have updated my profile.

 

[RobP]

Thanks - glad to have been able to help.

Appreciate the feedback.

Rob