Modifying A Sumproduct Formula To Search For Words In A Cell
Jul 7, 2009
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)
The formula contained inside the names are:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
View 2 Replies
ADVERTISEMENT
Mar 19, 2014
I've been working on a method to search a single column of 1000+ cells containing a paragraph with a 250+ word keyword list (also in a single column). I'm needing only complete words to be searched/ displayed regardless of spacing & punctuation. I've tried multiple methods the past few days and the one that worked (Macro with 4 complex formulas) was unusable when scaled out to all 1,000 rows due to the extremely long calculating time. I don't know VBA yet but was able to modify and create a user defined function that does everything I need except it displays string results that are not whole words (Ex. search for air, word in cell is fair, displays a result for air).
PHP Code:Â
Function RangeSearch2(text As String, wordlist As Range, seperator As String, Optional caseSensitive As Boolean = False)   Â
Dim strMatches As String    Dim res As Variant    Dim arrWords() As String    Dim skip As Boolean    skip = False   Â
On Error Resume Next    Err.Clear    For Each word In wordlist            If caseSensitive = False Then     Â
[Code] ...........
View 4 Replies
View Related
Jun 4, 2009
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
View 2 Replies
View Related
Jul 4, 2013
How would I modify this concatenation formula:
Code:
=concatenate(A2&" "&B2)
I would like it not to put a space in if A2 is blank.
View 2 Replies
View Related
May 8, 2006
Is it possible to use the Sumproduct formula to search for a particular word
and grab the amount in the next column, but 1 row down? For example, I would
search for region 1, but want to grab the figure in the total loans column
next to Consumer.
Region Loan Type Total Loans
--------- ------------ --------------
Region 1 Commercial 150000
Consumer 75000
Region 2 Commercial 90000
Consumer 145000
View 9 Replies
View Related
Feb 6, 2012
I'm trying to find if the description in Column L has any of the following words: (irrigation, sprinkler, water). The cell does not need to have all of the words, but just 1 to make the IF formula return a yes. view the example below and let me know where I am making the mistake. I don't want the case to be sensitive.
This is what I have so far, but it is only finding the first word.
This example returns nothing:
Text in Column L
Main irrigation line broken Need to repair
Formula in Column S
=IF(ISNUMBER(SEARCH({"sprinkler","irrigation","water"},L203)),"Yes","No")
This example returns Yes:
Text in Column L
Irrigation broken sprinklers and broken lateral li
Formula in Column S
=IF(ISNUMBER(SEARCH({"sprinkler","irrigation","water"},L210)),"Yes","No")
View 1 Replies
View Related
Jun 9, 2014
So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
View 14 Replies
View Related
Nov 7, 2008
Search Column A for a name (using an array if possible). If it finds this name, add a string of text to the AA cell on the same row that it found the name on.
For example,
If in A1 it finds the specified name. It then adds a string of text to AA1.
View 2 Replies
View Related
Aug 12, 2008
How do I use a sumproduct for a column with the words "YES", "NO", or "MAYBE" appearing?
I'm using
sumproduct(--$C$1:$C$50000="YES"),--($C$1:$C$50000="NO"),--($C$1:$C$50000="MAYBE"))>1
View 9 Replies
View Related
Jun 2, 2014
Below is a formula that I am attempting to modify:
From this:
=IFERROR(IF(A8<MIN(A$8:A$30)+365*5+1,(IF(AND(A8>=$E$4,A8<=$E$5),1,0)*B8*(MAX(C8,D8)/365)+B8),(IF(AND(A8>=$E$4,A8<=$E$5),1,0)*B8*(D8/365)+B8)),"")
To this:
=IFERROR(IF(A8<MIN(A$8:A$30)+365*5+1,B8*(MAX(C8,D8)/365)+B8),B8*(D8/365)+B8)),"")
But it is giving me an error result.
View 2 Replies
View Related
Mar 7, 2012
I have this function that checks a postcode and returns a value using the first part of the postcode.
What i would like to know is how to modify the function so if there is only the first part of a postcode already entered, it looks for that.
At the moment sometimes i get #N/A even though i know that value is in there.
Code:
=INDEX(U$4:U$1068,LOOKUP(1068,MATCH(IF({0,1},LEFT(B338,FIND(" ",B338)-1),LEFT(B338,1+ISERR(MID(B338,2,1)+0))),T$4:T$1068,0)))
View 1 Replies
View Related
Oct 25, 2013
I am trying to create a weighted average which will skip any row when Column B say's "yes" and then if Column N contains, a 0, I would like that to be skipped as well. The below works for skipping any row with the word "Yes", but it still includes 0 in the weighted average. Also, let's say the Column N contains a word and 0's, how can I skip that?
=SUMPRODUCT(($B$13:$B$15="Yes")*($I$13:$I$15)*(N13:N15))/SUMIF($B$13:$B$15,"Yes",$I$13:$I$15)
View 5 Replies
View Related
Apr 22, 2009
I am trying to do what I thought was a simple look-up. On one sheet in colum A I have some text and in colum B I have a number. On a second sheet i have a list of text that may or may not match the text on the first sheet in colum A. If the text in colum A matches one of the text items in the list, I want to have the value in colum B appear next to it in colum C. I tried to modify a vlookup formula but would only get a value error if there was a match.
View 3 Replies
View Related
May 20, 2007
"Use a formula to fill in column F (brand name) in the data worksheet. The Brand Name is the Branded Description minus the last word.
NOTE extra mark: If your formula can’t find a space (is error = true) then it takes whatever is in the cell and uses that."
Would I be using the CONCANATE formula or something similar?
View 9 Replies
View Related
Jul 20, 2012
I need a functionality in Excel, but I can't get it done. I want to search multiple titles on multiple words.
1. I have a list with titles (tab A)
2. I have a list with words (tab B)
Now I want to scan all titles on words that appear on tab B and return the value of the word.
Example:
Title (tab A - column A)
Return (tab A - column B)
Words (tab B)
BrandX Shampoo Big
Shampoo
Eau de Toilette
BrandX Nailcutter
Nailcutter
Nailcutter
BrandX Eau de Toilette
Eau de Toilette
Shampoo
So for row 1 the word "shampoo" is reconised in the title and returns this value. Same for the other rows. I need the functionality like the formula below, but then with more words (from a list). I don't want to work with "OR" because the list with word is about 200+ and will be more in future.
=IF(ISERROR(SEARCH("shampoo",A1)),"x","shampoo")
View 3 Replies
View Related
May 27, 2013
I am trying to search words that are in column A (5 letter combinations of sequences) within the text in column B (amino acid sequence).
So I am stumbling upon 2 questions:
- what is the function that would do this search.
- how to acchieve to reverse the text in B1 cell (eg. abcde --> edcba , but with 600 letters),
sample file: test.xlsx
View 4 Replies
View Related
Feb 17, 2010
I want to search say in column A for specific words say “Don” and then write in another column (say column 2) as “one”. Then it should keep searching for specific word until the last cell in that column and keep writing specific word in another column. It should do nothing if that specific word is not found.
I was able to use IF(ISERROR) but there is limitation of max 6 searches. Also i was able to achieve it by so many iteration of IF(ISERROR) and using autofilter.
My goal is to search around 30 to 40 criteria in one shot or by writing macros.
I guess i have presented my problem properly.
View 4 Replies
View Related
Jun 8, 2006
I am looking to search a column of data (text) and have copy relevant cells to another spreadsheet.
Example:
Enter "Cat" in a userform, and the program would copy all of the following rows to a new spreadsheet:
Cat
cat
your cat
the cat
123 cat
cat123
catters
CAT
Think you guys may get the picture...if cat appears in any way in the cell, I would like that cells contents on a new spreadsheet.
View 3 Replies
View Related
Jul 19, 2006
I have imported text from a database that has names shown as "last name, first name" in a single cell and am trying to figure out if it is possible to modify those names to "first name last name." I would also like to see if I could just have the first name transferred to a different cell.
View 3 Replies
View Related
Dec 6, 2010
I am suffering with split a long descriptions into 3 cells with criteria 1st cell not more than 30 characters, 2nd cell not more than characters and 3rd cell will locate the remaining characters there. I think this is quite easy if I use LEN/MID/RIGHT/LEFT formula. However, I wish the formula will smart enough to split word by word. refer to example below:-
"My lecturer replied, that i really did very bad in final, nothing's gonna change my plan."
If I use left(A1,30) formula, the result is "My lecturer replied, that i re"however, the word "really" is cut half way. I am finding the formula that split description to not more than 30 characters and won't cut my string and become incomplete word. Expected result should be 1st cell "My lecturer replied, that i", then "really did very bad in final," at 2nd cell.
View 5 Replies
View Related
May 10, 2009
I have a key-word, and I need to search a 90,000 words in a range where i should find several words that have 2, 3 or 4 letters of the first letter of the key-word, then find several words that have 2, 3 or 4 letters of the second letter of the key-word,
View 9 Replies
View Related
May 27, 2013
I do not have excel or an xls for a sample, I will try and restate the question.
In Cell A1 It will be used for copying the contents of and pasting the completed information into web browsers.
In Cell A1 You will find This information contained and formatted in one cell alone.
Figure-1
A1=
Information About Company
Brief Story About Company
Product Name: (X)
UPC Number: (Y)
Serial Number: (Z)
Terms Of Sale
Etc
End Figure-1
I want to essential Modify (X)(Y)(Z) using different cells one for each variable. "All completed information will be found inside of A1"
Having Cell B1-B9 Being used as labels "Product Name" "UPC Number" "etc" and using cell C1-C9 to input variables for = (X)(Y)(Z)(ETC)
In reality from cell A1 will change dynamically, but some information will need to stay constant.
Every thing In B column will just be labels.
Every thing In C will be for variables.
View 1 Replies
View Related
Aug 28, 2012
With the formula I have as:
=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01)
I want to dis-include the CC="06" in the summation. I tried using:
=SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)
But it gives a wrong calculation
I use "--" because i have character in the cell
View 1 Replies
View Related
Oct 22, 2008
I'm trying to change a
418,3315555
format cell to a regular phone number cell
418-331-5555
View 9 Replies
View Related
Jun 16, 2009
To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.
View 4 Replies
View Related
Dec 7, 2011
I am using excel 2007. I have a worksheet with a list of words I wish to step through this list replacing the selected words in another workbook with nothing (ie deleting them).
I have the following code
Code:
Sub replacewords()
Dim MyWord As String
Dim wbLibrary As Workbook
Dim wbWorking As Workbook
Dim myExcelColumns As Integer
Dim myExcelRows As Integer
Dim MyRow As Integer
Dim MyRange As Range
[code].....
It does not seem to evaluate "myRange";
View 2 Replies
View Related
Oct 30, 2009
I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.
I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.
=SUMPRODUCT(--('Books List'!G2:G1166=D8))
View 9 Replies
View Related
Aug 21, 2009
I've got the following sumproduct formula (which seems to work):
=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))
B17 is a keyword to be found in the Product_Keywords range
I'd like to modify it so that:
ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1
Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?
View 9 Replies
View Related
Jan 24, 2010
I need to check each of 13,000 job titles to see if they include the string "VP".
For the time being, I want to find the most efficient way to check an individual title, before building an array or SUMPRODUCT function that will check all the titles in swoop.
So... I have tried:
=ISNUMBER(SEARCH(A1,B1))
..but it returns TRUE if A1 and B1 are both blank -- which they can be in my spreadsheet.
And I have tried:
=SIGN(SEARCH(A1,B1))
but it returns #N/A if A1 cannot be found within B1.
If I can, I want to avoid introducing an IF statement because, looking ahead, I am already concerned that this analysis will be complicated enough without multiple paths.
So.. what's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether A1 is in B1?
EXTRA-CREDIT!
An extension of the above problem... arrays and SUMPRODUCTs welcome:
What's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether (A1 or A2 or A3... or A99) is in B1?
View 13 Replies
View Related
Apr 12, 2014
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx‎
1.xlsx‎
2.xlsx‎
View 2 Replies
View Related
