Solved more than one item in Excel formula

February 1, 2016 at 04:36:10
Specs: Windows 7
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.


See More: more than one item in Excel formula

Report •


✔ Best Answer
February 1, 2016 at 09:14:18
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



#1
February 1, 2016 at 05:59:43
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


Report •

#2
February 1, 2016 at 07:17:00
This one is a bit more specific:

=IF(OR(A15="KASAF 1",A15="KASAF 2",A15="KASAF 3-5"),1,"")

MIKE

http://www.skeptic.com/


Report •

#3
February 1, 2016 at 07:54:55
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.


Report •

Related Solutions

#4
February 1, 2016 at 07:58:31
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?


Report •

#5
February 1, 2016 at 08:54:14
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.


Report •

#6
February 1, 2016 at 09:00:01
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.

Report •

#7
February 1, 2016 at 09:14:18
✔ 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


Report •

Ask Question