Need a formula that can sum the point value of a given list of categories for a person

[Sneaky323]

Hello Excel Guru's,

I need a formula that can fulfill this requirement:
1. Read name in sheet 2
2. Identify the name on multiple rows in sheet 1.
3. Read and Identify each category(6 categories total) that the individual is connected to in sheet 1, and sum the point value of the combined categories from a table in sheet 2.

I have tried a variety of IF, IF(AND, SUMIF, SUMIFS, VLOOKUP, INDEX(MATCH; with no avail.
Sheet 2 is where I have an array defined as "Category" which has the point value associated with each category.


***Another way of me getting this idea across*** So in Sheet2 Cell E2 I need a formula to read the name in sheet2 Cell C2, Go into Sheet 1 and examine the entire Sheet1!C:C. When it finds a match it needs to then go directly accross on Sheet1 to Column A and read the category but instead of reading the category, it takes the point value assigned to the category and stores it to be summed up after the formula has identified all matching criteria. If done correctly the answer in Sheet2! E2 for "Barb Throckmorton" would be 18.

Here is the excel sample attachment with all data regarding the above.

Thanks for the help!

[MarvinP]

Hi Sneaky and welcome to the forum,

See the attached where I've added a Helper column on sheet1 that does a vlookup back to your categories to put in values. Then on sheet2 is a SumIfs formula to give what I think you want.

[Sneaky323]

Hi Marvin,

Thanks for the quick response. I had suggested something like that to the clients, but they did not want another column listing the point value for the categories. I could hide it, but if they ever exported more data, I'd have to explain my devious ways! lol

[MarvinP]

Because your problem is a "two step" type of problem I find adding a Helper column is MUCH easier to do, explain, and/or fix than a more complicated solution. If I wrote some VBA function to do the same work, people would need to find the code and understand it enough to expand it to work.

Go with the Helper Column and hide it if you need to. If they discover it, claim it is a better method than most others.

OK - so find another method that uses Pivot Tables and doesn't need a Helper Column. See it they might like this answer better...

[Sneaky323]

Thanks again Marvin. I'll try in Pivot table and see what they say.

[Tony Valko]

First, you have trailing space characters in the Name columns on both sheets. You should clean that up!

Try this...

This data needs to be sorted in ascending order based on the category.

Data Range

	A	B
1	Category	Value
2	Charter	1
3	Cost	5
4	Pres	4
5	PRJ	3
6	Proc	2
7	Report	6

Data Range

	A	B	C	D
1	Category	Value	Name	Point Total
2	Charter	1	Barb Throckmorton	18
3	Cost	5	Danilo Cowie	36
4	Pres	4	Hattie Essary	11
5	PRJ	3	Jeffie Porto	33
6	Proc	2	Omar Caiazzo	17
7	Report	6	Phillis Nation	4
8			Sau Lisby	30
9			Tess Prosser	10
10			Thaddeus Heinze	22
11			Velva Lyke	16

This array formula** entered in D2 and copied down:

=SUM(IF(Sheet1!C$2:C$57=C2,IF(ISNUMBER(MATCH(Sheet1!A$2:A$57,A$2:A$7,0)),LOOKUP(Sheet1!A$2:A$57,A$2:B$7))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FlameRetired]

Another array formula in D2 and filled down.

Please Login or Register  to view this content.