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-5Please help.

See More: more than one item in Excel formula

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)message edited by DerbyDad03

#1
February 1, 2016 at 05:59:43

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,"")MIKEhttp://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. ;-)

Report •

Related Solutions

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

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