SUMPRODUCT, COUNTIF, LEFT(SEARCH) help!

[Elephrunner]

Hello. So I have a question to help calculate a correct item sum without a helper column and I haven't been able to figure out how to get the whole formula for what I need. Sorry this is kind of long... but I want to give enough information to explain my question... I can get pieces of this to work, but can't put it all together.

So... data is based on an assessment... (Item #'s & Answers)...person may start at an item after 1 because of their age - like if you are older than 10... start at item 4... the BASAL is the number of items a student has to get consecutively correct at the beginning of the assessment in order to keep going (for this example, I said 5 in a row)... when SCORING... Items BELOW the BASAL - are scored 1... so for the example below... The basal # is item 8... and even though responses started at Item #4 & not #1... they will get credit for "correct" responses for items 1,2, & 3.

To additionally evaluate the items, they are broken into smaller item set lists...see the SCORE & WORD columns ... the subset item #'s are NOT necessarily consecutive (so in the word list - you can see the items are 1 - 2.. and then 5, 6, 7... etc...& they are TEXT formatting. I have figured out the formulas to put the scores from the test responses in the SCORE column for the corresponding item numbers in the subset...

BUT... what I now need to do is calculate the TOTAL SCORE... that will add in values of "1" for items below the BASAL and then sum the count for all the items... So for any item # less than or equal to C3 (8)... it should have a score of 1... so for the data below, the CORRECT total score would be 7.

I need the formula to extract the item # from the WORD column...compare it to the Basal Item # (C3 --> or 8 in this example) and if the item is <=8 (C3)...it gets a value of 1...items after the BASAL are scored based on the value already on the sheet (items 6-15 would get the score already marked). I have figured out how to do a LEFT & SEARCH combination to pull out the #'s from the text string, but I can't figure out how to get it to then take that value and compare it to the BASAL value and do the total count based on whether it is<= the BASAL #.

To get the correct 1/0 values in the SCORE column based on the ANSWER column, I used a SUMPRODUCT formula and got that part to work.

Another issue is that I don't want ANY VALUES to SHOW in the cells below the basal... I want them to stay blank (Score 1, 2, 5 - because I don't want it to look like those items were given). I just want the total in D12

Here is the formula part that I got that counts the total number of items in COLUMN E
=SUMPRODUCT(COUNTIF($A$2:$A$16,--(LEFT($E$1:$E$10,SEARCH(".",$E$1:$E$10)-1))))

BUT, it doesn't look at the BASAL... and it doesn't put in the actual attained scores for item 11 & 15... (This is the part that I got to work based on the formula I used to get the actual attained 1 or 0 score in column D... what I thought I wanted was to put the LEFT(etc.) portion at the beginning with some comparison to being <= C3...but I can't figure out how to get it to accurately pull the #'s from Column E in order to then compare it to C3...

Long winded question.. sorry about that - but any ideas... (And Formulas please as I am not familiar with VBA)

Thanks in advance!

Example Data:

ROW	COLUMN A	B	C	D	E
1	Item:	Answer	Basal	Score	Word
2	1		5 Correct in a row		1. cat (noun)
3	2		8		2. bowl (noun)
4	3				5. banana (noun)
5	4	1		1	6. wet (adjective)
6	5	1		1	7. umbrella (noun)
7	6	1		1	10. green (adjective)
8	7	1		0	11. zebra (noun)
9	8	1		1	12. washing (verb)
10	9	0		0	15. drums (noun)
11	10	1
12	11	0		????	TOTAL SCORE
13	12	1
14	13	1
15	14	0
16	15	0

<tbody>
</tbody>

 

[Elephrunner]

I FINALLY Figured it out...

{=SUM(IF(--LEFT($E$2:$E$10,SEARCH(".",$E$2:$E$10)-1)<C3,1,IF(--LEFT($F$2:$F$10,SEARCH(".",$F$2:$F$10)-1)>=C3,$D$2:$D$10,"ERROR")))}

The Excel Is Fun You Tube videos have been so great at helping me learn a variety of components... and after several weeks of messing around... I finally figured out how to get the components together to solve the problem... YEAH!!!!