hi I'm trying to find a formula to calculate 3 variations in a column, what I currently have is: =IF(A15="K*",1,"")

I want a formula to put a 1 in another column if column A has the following in it KASAF 1, KASAF 2 or KASAF 3-5

Please help.

✔ Best Answer

SUMPRODUCT might work for you: =SUMPRODUCT(--(LEFT('2015'!A:A,1)="K"),'2015'!AC:AC)

It might be safer to use this in case something else in Column A starts with a K:

=SUMPRODUCT(--(LEFT('2015'!A:A,5)="KASAF"),'2015'!AC:AC)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

How about this? Modify as required. =IF(LEFT(A15,1)="K",1,"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

This one is a bit more specific: =IF(OR(A15="KASAF 1",A15="KASAF 2",A15="KASAF 3-5"),1,"")

MIKE

So many options ;-) Case Sensitive

=IF(OR(ISNUMBER(FIND({"KASAF 1","KASAF 2","KASAF 3-5"},A15))),1,"")

Not Case Sensitive

=IF(OR(ISNUMBER(SEARCH({"KASAF 1","KASAF 2","KASAF 3-5"},A15))),1,"")

I think the final solution depends on how much of the 3 choices are common to each cell being searched.

Since lambea started with

K*, I'm thinking that anything that includes the entire 3 strings ("KASAF 1","KASAF 2","KASAF 3-5") might be overkill. If those are the only pieces of data that start with a K, then that is all we need to search for.As Janet Yellen keeps saying, the final solution will be data dependent. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi That worked great!!

Now I need to pull the data from sheet to another, my formula used to be:

=SUMIF('2015'!A:A,"=AP",'2015'!AC:AC)The form has now changed it's name and we now have three versions, as in my previous question, therefore, I need to find a way of getting the same results but replacing AP with KASAF 1, 2, or 3-5.

I've tried just replacing the "=AP" with "=K*" but that doesn't work.

Any suggestions?

re: "That worked great!!"We offered a few different suggestions. When you say

"That worked great!!"we don't know which "that" you are referring to.Bottom line, wildcards (*) do not work in the IF function(s).

Please clarify the issue related to your search strings. Since you have now tried to use "K*" twice, can we assume that all of the search strings you are looking for start with a "K"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes all three words start with K KASAF 1 KASAF 2 KASAF 3-5, I just need to be able to pull the total number of KASAFs through from one spreadsheet to another one that calculates the totals.

I used to use =SUMIF('2015'!A:A,"=AP",'2015'!AC:AC) when the document was called AP but now we have 3 KASAFs instead, but still need to total all of them up together.

SUMPRODUCT might work for you: =SUMPRODUCT(--(LEFT('2015'!A:A,1)="K"),'2015'!AC:AC)

It might be safer to use this in case something else in Column A starts with a K:

=SUMPRODUCT(--(LEFT('2015'!A:A,5)="KASAF"),'2015'!AC:AC)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll