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

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History