Add Multiple Values Based on Criteria

Add Multiple Values Based on Criteria

Search for the word "Clear" in the Task column.
If the word "Clear" is found, go to Value column.
Add all values starting from "Clear" down to the end of the list.
If it is larger than 10, return the word "OK". Otherwise "Poor".
If larger than 20, return the word "Good".
The length of the list is variable.

Here is the sample worksheet (Please click).

How should I do?
Thanks a lot.

what if
1 no match for clear
2 if sum larger than 20

one way
=IFERROR(LOOKUP(SUM(INDEX(B:B,MATCH("clear",A:A,0)):INDEX(B:B,MATCH(10^99,B:B,1))),{0,10.000001,20},{"poor","ok","good"}),"clear not found")
if its whole numbers only
=IFERROR(LOOKUP(SUM(INDEX(B:B,MATCH("clear",A:A,0)):INDEX(B:B,MATCH(10^99,B:B,1))),{0,11,20},{"poor","ok","good"}),"clear not found")
or you could roundup the sum

I understand that you want the total for all the values in all the value columns that match CLEAR in the Task columns.

Formula:

Please Login or Register  to view this content.

Originally Posted by newdoverman

I understand that you want the total for all the values in all the value columns that match CLEAR in the Task columns.

Sorry that I was not clear.
Not all of them. Each table is treated separately.

Also there will be other data in the actual worksheet.
It would be the best it can detect the end of the list intellectually so I can apply the same formula to all tables universally.

ok a modification

Formula:

Please Login or Register  to view this content.

since larger than 10 can be 10.1
this formula rounds up anything with a decimal to the next integer
so 10 stays 10 10.0001 rounds up to 11