Sumifs or sumproduct not returning correct value

[Liz_I3]

I want to sum accross columns H9 to EC9 if columns H2 to EC2 equals the value in F2 or F3 . F2 and F3 will always be a number. But in my formula I want to concatenate it will the Letter "S".

This formula retruns a 0 ( not correct) =SUMPRODUCT(($H$2:$EC$2=$F$3&"S")*($H$2:$EC$2=$F$2&"S")*($H6:$EC6))

if I leave out one of the conditions =SUMPRODUCT(($H$2:$EC$2=$F$3&"S")*($H6:$EC6)) I get the correct answer for that condition, but I cannot get it to work for both conditions

I have also tried =SUMIFS(H9:EC9,H2:EC2,F2&"S",H2:EC2,F3&"S") which also returns 0

please what am I doing wrong?

Thanks
L

 

[MrKowz]

The problem is that in those formulas, you are testing for when BOTH conditions match, and since only one or the other condition can match, it is always returning a FALSE statment in one of the two conditions.

Try:

=SUMPRODUCT(--(($H$2:$EC$2=$F$3&"S")+($H$2:$EC$2=$F$2&"S")),$H6:$EC6)

 

[T. Valko]

I want to sum accross columns H9 to EC9 if columns H2 to EC2 equals the value in F2 or F3 . F2 and F3 will always be a number. But in my formula I want to concatenate it will the Letter "S".

This formula retruns a 0 ( not correct) =SUMPRODUCT(($H$2:$EC$2=$F$3&"S")*($H$2:$EC$2=$F$2&"S")*($H6:$EC6))

if I leave out one of the conditions =SUMPRODUCT(($H$2:$EC$2=$F$3&"S")*($H6:$EC6)) I get the correct answer for that condition, but I cannot get it to work for both conditions

I have also tried =SUMIFS(H9:EC9,H2:EC2,F2&"S",H2:EC2,F3&"S") which also returns 0

please what am I doing wrong?

Thanks
L

Try it like this...

=SUMPRODUCT(--(ISNUMBER(MATCH(H2:EC2,F2:F3&"S",0))),H9:EC9)

 

[Liz_I3]

Thanks both formulas worked

I did not realize that the * represented the "and" operator.

Thanks

L