SUMIF with MID?

Question

I have an expression that doesn't work:

=IF(A10="","",SUMIF(C8:U8,UPPER(MID(C8:U8,1,4))="TEST",C10:U10))

My objective is to SUM that values in C10:U10 that correspond to a "TEST" in the first four (4) characters of the respective column cells of C8:U8.

Essentially, here's an example...

  Column =>                C                D                E              F             G               H   ....

Row 8                        Test01        Reck01      Test02     Reck2      Test03        Review03

Row 9....

Row 10                      92.5            14.6           86.1        8.9         76.8           -1.2

I just want the 92.5, 86.1 and 76.8, bolded and underlined in the above example, SUM'd and ignoring the other values.  I typically put the "=TEST" in the 'criteria' but this time I just want the first FOUR (4) characters (the "Test" or Upper("Test")=TEST and ignoring character positions 5 and above of their respective columns.

A10="" is a validation that data exists in the row so it's not important here relative to the SUMIF question.

Thoughts?

Thanks

Answer 1

Let's say you want to sum values where the cell in row 8 ends in "TEST". You'd use "*TEST" as condition.

Or you want to sum values where the cell in row 8 contains "TEST" in any position (start, middle, end). You'd use "*TEST*" as condition.

Answer 2

Try

=IF(A10="","",SUMIF(C8:U8,"TEST*",C10:U10))

Answer 3

Let's say you want to sum values where the cell in row 8 ends in "TEST". You'd use "*TEST" as condition.

Or you want to sum values where the cell in row 8 contains "TEST" in any position (start, middle, end). You'd use "*TEST*" as condition.

THANKS!!

Answer 4

Works... That was too easy... should have caught that!  

Is there a way to use substrings and midstring searches with SUMIF/SUMIFS or and *IFS?

Thanks!!