I am using this formula.

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$K$3:$K$60000="050S0F1");TMS2!$F$3:$F$60000))

Is it possible to modify this part (TMS2!$K$3:$K$60000="050S0F1") to set the
criteria to look for all cases where the cell equals 050S0F1, 050S0F2 ...
050S0FB. In other words, any cell where the left 6 characters are 050S0F ?

Thank you in advance.

Replace ; with a comma in LEFT() ?

Alok Joshi

"Peo Sjoblom" wrote:

It is better to use sumproduct and enter the formula normally

=SUMPRODUCT(--(TMS2!$BL$3:$BL$60000<=WatchList!$A$4);--(LEFT(TMS2!$K$3:$K$60000;6)="050S0F");TMS2!$F$3:$F $60000)


Regards,

Peo Sjoblom

"carl" wrote:

I am using this formula.

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$K$3:$K$60000="050S0F1");TMS2!$F$3:$F$60000))

Is it possible to modify this part (TMS2!$K$3:$K$60000="050S0F1") to set the
criteria to look for all cases where the cell equals 050S0F1, 050S0F2 ...
050S0FB. In other words, any cell where the left 6 characters are 050S0F ?

Thank you in advance.

No, look at the OP's original post, he's obvisoulsy using Eurpoean delimiters

Regards,

Peo Sjoblom

"Alok" wrote:

Replace ; with a comma in LEFT() ?

Alok Joshi

"Peo Sjoblom" wrote:

It is better to use sumproduct and enter the formula normally

=SUMPRODUCT(--(TMS2!$BL$3:$BL$60000<=WatchList!$A$4);--(LEFT(TMS2!$K$3:$K$60000;6)="050S0F");TMS2!$F$3:$F $60000)


Regards,

Peo Sjoblom

"carl" wrote:

I am using this formula.

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$K$3:$K$60000="050S0F1");TMS2!$F$3:$F$60000))

Is it possible to modify this part (TMS2!$K$3:$K$60000="050S0F1") to set the
criteria to look for all cases where the cell equals 050S0F1, 050S0F2 ...
050S0FB. In other words, any cell where the left 6 characters are 050S0F ?

Thank you in advance.