Search for cell value; look up last number value in row, sum those values

Hello,

I have a changing database where I give a questionnaire(s) and then record the score. Sometimes I do not administer the questionnaire and use "*" to denote that I met with the person, but did not administer anything. There are also blank cells where a week went by and I neither met with them nor were questionnaires administered. So there are blanks and *'s everywhere making things more complicated. Furthermore, I will be continuously adding data to both the rows and the columns. I attached an example of what I am trying to accomplish.

Sorry if this is confusing, I had a hard time making this into a question. I have tried using an Index/Match formula but cannot figure out what goes in the "column portion of the Match. What I have looks like: =INDEX(B1:EZ3000,MATCH("Red",A1:A3000,0),?) with the ? mark denoting what I cannot figure out.

Thank you so much in advance. If you have the time, I would also not take for granted an explanation of what you came up with so I can learn and not bother you all as much in the future

Thank you,

Greg

Hi Greg- If a (hidden??) helper column is allowed, use a formula in each row to find the last number entered (B2 and copied down):

Please Login or Register  to view this content.

...and a simple SUMIF to finish (Shown in C13):

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G	H	I
1	Name	Hidden	S	S	S	S	S	S	S
2	Red	2	1		*		2
3	Blue	4	3			4
4		#N/A
5	Green	2	2		2
6	Red	1	1		1	*
7	Red	1	4	*		1
8		#N/A
9		#N/A
10	Blue	1	1	*
11		#N/A
12
13	Red		4

formula for G2

Formula:

Please Login or Register  to view this content.

Then fill down and sum.

That should be enough to get you started.

You're welcome, and thank you for the rep! If complete, please go to Thread Tools (up top) and mark your thread as SOLVED. -Lee