Modifyiing a SumIF Formula to look for "OLA*
 

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.

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

--
Regards
Frank Kabel
Frankfurt, Germany
"carl" schrieb im Newsbeitrag
...
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.

thanks frank. i'm using the following formula:

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$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.
"carl" wrote:

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.

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

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$6 0000)

which just needs enter.

carl wrote:
thanks frank. i'm using the following formula:

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$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.
"carl" wrote:


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.

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)*(RI GHT(TMS2!$K$3:$K$60000,3)="OLA");TMS2!$F$3:$F$6000 0))



On Mon, 3 Jan 2005 09:59:01 -0800, "carl"
wrote:

thanks frank. i'm using the following formula:

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(T MS2!$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.
"carl" wrote:

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.