Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
=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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions | |||
Formula help SUM(IF((... | Excel Worksheet Functions | |||
IF or SUMIF help for Overtime Formula | Excel Worksheet Functions |