Modifyiing a SumIF Formula to look for "OLA*

[Guest]

I tried modifying my SUMIF formula to sum if the data in COLK contains "OLA".
The modifiaction does not appear to work.

If OLA does appear in COLK, it will always be the right 3 characters.

Can my formula be modified to sum if COLK has OLA in the right 3 characters ?

Thank you in advance.

 

[Frank Kabel]

Hi
=SUMIF(A:A,"*OLA",B:B)

 

[Guest]

thanks frank. i'm using the following formula:

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

I have the additional criteria TMS2!$BL$3:$BL$60000<=WatchList!$A$4.

is there a way to modify this formula to sum based on "OLA" in COLK ?

I tried using the * wildcard in my formula (as in above) but it did not work.

regards.

 

[Aladin Akyurek]

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(RIGHT(TMS2!$K$3:$K$60000,3)="OLA");TMS2!$F$3:$F$60000))

which must be confirmed with control+shift+enter.

Equivalently...

=SUMPRODUCT(--(TMS2!$BL$3:$BL$60000<=WatchList!$A$4);--(RIGHT(TMS2!$K$3:$K$60000,3)="OLA");TMS2!$F$3:$F$60000)

which just needs enter.

 

[Myrna Larson]

But THAT isn't a SUMIF formula. It doesn't use the function SUMIF. It's an
array formula using SUM(IF(..... Obviously they aren't the same thing.
Wildcards won't work in this situation. Try it this way:

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(RIGHT(TMS2!$K$3:$K$60000,3)="OLA");TMS2!$F$3:$F$60000))