IF SUM Formula

[PK]

I am trying to sum part of a cell and group by the initial letter of that
formula.
Example - data in cells A1 to G1 as follows:
h7.5 s5 blank cell h7.5 s7.5 blank cell h10.5
I would like to sum all numbers that have a pre-fix of "h" in their cell.

Thanks.

 

[Gary''s Student]

In A2 enter:
=LEFT(A1,1) and copy across

In A3 enter:
=--IF(LEN(A1)=0,0,MID(A1,2,255)) and copy across

We see:

h7.5 s5 h7.5 s7.5 h10.5
h s h s h
7.5 5 0 7.5 7.5 0 10.5

Finally:
=SUMPRODUCT((A2:G2="h")*(A3:G3))
will display 25.5

 

[מיכ×ל (מיקי) ×בידן]

You need NO helper-rows what so ever...
In cell H1 put this array-formula and :
Type the hereunder proposed Array-Formula and press CTRL+SHIFT+ENTER at the
same time rather then just ENTER.
DO NOT type the curly braces { }. Excel will display them automatically !
{=SUM(IF(ISNUMBER(VALUE(SUBSTITUTE(A1:G1,"h",""))),VALUE(SUBSTITUTE(A1:G1,"h","")),0))}