SUMPRODUCT and LEFT

[Mark McInerney]

Hi Folks - Thanks in advance for taking the time to look at this - appreciated.

I am trying to use SUMPRODUCT but the valed returned is "0" when I am expecting "53"?

Is my syntax correct?

Thanks - Mark.

 

[awoohaw]

try using an asterisk instead of a comma.

Also, have you examined each component of the calculation?

or try this:

=SUMPRODUCT((--(LEFT(B3:B4,4)=LEFT(B3,4))*(C3:C4)))

 

[Mark McInerney]

Thanks - I should have spotted that!!! Many Thanks.

 

[awoohaw]

my pleasure, best wishes.

 

[FormR]

Hi,

=SUMPRODUCT((--(LEFT(B3:B4,4)=LEFT(B3,4))*(C3:C4)))

I'd say that's a tad over egged - either of these should suffice:

=SUMPRODUCT(--(LEFT(B3:B17,4)=LEFT(B3,4)),C3:C17)

=SUMPRODUCT((LEFT(B3:B17,4)=LEFT(B3,4))*C3:C17)

Or SUMIFS() might be a better choice overall.

=SUMIFS(C3:C17,B3:B17,LEFT(B3,4)&"*")