Thread: Sum with criteria only numbers in range that contains text

1. Sum with criteria only numbers in range that contains text

Hello,

I have this specific range with numbers and " " (which means that the cell is blank, but contains space -> isn't a number) :

 1 A B C D E F G H I J 2 Criteria column jan fév mar apr mai juin july aug sept 3 Name 1 " " " " 5 12 17 22 25 21 23 4 Name 1 " " " " 3 7 5 14 19 20 18 5 Name 2 " " " " " " " " " " 9 12 16 22 6 Name 2 " " " " " " " " " " 3 4 6 8 7 Name 3 " " " " " " 0 3 5 7 8 9 8 Name 3 " " " " " " 0 0 1 3 5 8 9 Name 4 " " " " " " " " " " " " 12 24 30 10 Name 4 " " " " " " " " " " " " 9 16 27

Then I need to sum numbers (in cumuluative way) according to :

Criteria 1 : Name 2
Criteria 2 : august
the result will be : 9+12+16+3+4+6 =50

Criteria 1 : Name 4
Criteria 2 : sept
the result will be : 12+24+30+9+16+27 =118

Cheers,
Sam

2. Try this:

=SUMPRODUCT((\$A\$2:\$A\$9=M2)*(COLUMN(\$B\$1:\$J\$1)-1<=MATCH(M3,\$B\$1:\$J\$1,0)),\$B\$2:\$J\$9)

3. Thanks a lot AliGW for your time,

Your response is much appreciated, it works like a charm !

Have a perfect day !

4. You're welcome.