Sumproduct? Sumif? (search A Range Any Cell Containing Words Or Phrase "20%" )

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


ADVERTISEMENT

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 View Related

Search For Only One Word / Phrase In Range Of Cells

Jul 23, 2014

I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.

For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".

View 3 Replies View Related

Sumproduct Or Sumif Date Range?

Jan 27, 2008

Im Trying to use a formula of countif or sumproduct

so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.

[Code] ........

But this formula doest count.

I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .

View 3 Replies View Related

SUMPRODUCT / SUMIF With Named Range

Mar 26, 2007

Why won't this SUMPRODUCT work?

SUMPRODUCT(Sheet2!D1:D2000=OPEN,Sheet2!F1:F2000)

I have a range (D1:D2000) that contains a 'Status' value.

OPEN is a named range that refers to eight possible Status values: Assigned, New, Hold, Re-test, In Progress, Failed, Ready for Push, Coded

The formula should sum the values in F1:F2000 for any row containing one of the eight statuses in D1:D2000.

View 9 Replies View Related

Combine SUMPRODUCT, SUMIF, COUNTIF Within Date Range

Jan 13, 2007

The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.

I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.

=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))

View 11 Replies View Related

Search A Range Of Words, That Have 2 Or More Letters Of One Word

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

Phrase Search Macro

Jan 26, 2014

I would like to write a macro which checks if phrase from column B (lets say 13845) can be found in column A cells.

For example DDH113845G389.

I think that it should be some kind of loop but I'm afraid I can't handle it.

View 6 Replies View Related

Search For Keyword Or Phrase In Website Using Excel?

Apr 11, 2012

I would like to search for a keyword or a phrase in a website using excel.

Generally we copy the keyword or a phrase and paste it in google and go for search. but i wanted to do this using excel as i have to search same pharse in multiple websites.

View 1 Replies View Related

Search For A Phrase In Column - Function Return Error

Feb 18, 2013

I am trying to search for a phrase in a particular column but the excel search function is returning !Value as an error...

Both fields are text so nit sure what is wrong here.

I have a list of cities that i want to cross reference across a cloumn of notes on another sheet. So, I really want to search for the city (Sheet1 Cell A1) in Notes Sheet2 Cell A1) and I want to return it in Sheet1 Cell B1 Whether it exisits in the notes. I then want to copy this down my list of cities in Sheet1 Column a.

View 5 Replies View Related

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

View 9 Replies View Related

Search For Multiple Words Within Cell?

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

Search For Multiple Words In Cell If Certain Word Matches Produce Certain Value?

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

Search For An Array Of Words, Change A Cell Based On What It Finds

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

SUMIF Contains Either Words

Jul 17, 2012

I'm trying to sum up a range if it contains one or another word. For example, if column A contains the descriptions of products and column B contains the prices, I want to sum all the products that have either of the words "mont" or "blanc" or "starwalker" or all three.

View 9 Replies View Related

Sumproduct For A Column With The Words YES, NO, MAYBE

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

Macro To Only Keep 100 Words In Range Of Single Words Per Column?

Jun 3, 2014

I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.

I'd prefer to email the file if that's okay.

View 7 Replies View Related

SUMPRODUCT Or SUMIF?

Oct 21, 2008

ColAColBColC
1 FA4,000.00 10%
2 CB1,500.00 15%
3 FA7,500.00 15%
4 AA700.00 5%
5 JG250.00 5%
6 FA1,200.00 5%
7 AA900.00 5%
8 CB175.00 15%
9 FA2,000.00 2%

At the foot of the spreadsheet I would like to show the total of the calculation
Col B * Col C, if Col A shows a value of "FA"

Having veiwed similar posts, I believe SUMPRODUCT may be the correct formula, but I cannot seem to achieve the result I am looking for.

So in the example the desired result is 1,625 B1 * C1 + B3 * C3 + B6 * C6 + B9 * C9

View 9 Replies View Related

Sumif Or Sumproduct?

Feb 26, 2009

How can I write a conditional sum formula that will skip a criterion when it is left blank?

I was able to make this work:
=CHOOSE(COUNTA(B:B),SUMIFS(F1:F4,D1:D4,B1),SUMIFS(F1:F4,D1:D4,B1,E1:E4,B2))

where column B has the criteria.

I was wondering if there was a better way using Sumif or Sumproduct.

View 9 Replies View Related

SumIf Or SumProduct

Aug 23, 2009

in colum A i have a list of dates, in colum B i have a list of amounts. what i need to get is the sum of the amounts where the dates in colum A are between the dates in cells C1 & D1. Tries sum producst but all I get is the total sum of colum B irrelevant of the date!

View 9 Replies View Related

Sumif & Sumproduct (combined Together)

Oct 29, 2008

i would to combined these together

can this be done

View 2 Replies View Related

3 Conditions - SUMIF Or SUMPRODUCT Or...?

Jan 6, 2009

I have 4 columns, A B C and D. They are a mix of formats.

A= Name (text)
B= Date
C= Code (text)
D= Number

I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.

I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.

View 14 Replies View Related

SumIf/Sumproduct If Contains Detail

Mar 10, 2009

I have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name.

I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information:

=SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)

View 3 Replies View Related

A Sumif Of Sumproduct Question

Jul 9, 2009

I'm trying to do here is if a layaway has been paid off then there will be a tax amount in column C. IF there is tax in column C and column A has the word layaway (or the word way) in it then I need to find out how much the total of the layaway $ amount was. Meaning, to figure out how much the tax is for.

For example:
If the text in Column A contains the word way AND column C contains a $ amount then return the amount that the tax would be for. Of course this would be an accumulated amount for all of column C. So, if Layaway is in column A and $3.13 amount is in column C then it will return $50.00 as the original selling price. Tax here is .0625 %.

View 2 Replies View Related

Sumproduct Or Sumif Formula

Aug 24, 2009

a1=good
a2 = 100

b1 = bad
b2 = 50

c1 = good
c2 = 100

would like to sum a2+b2+c2 only if a1,b1,c1 = good.

View 6 Replies View Related

Sumif & Sumproduct Combination

Oct 9, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.

View 2 Replies View Related

SumProduct / Sumif, Within 2 Times

Jan 20, 2010

From some fleet data i am trying to total any journey which took place between two times.

The time format is hh:mm:ss. I want to total the miles travelled in each trip between 5pm and 5am for certain vehicles

Vehicle Registration is in colum A, the times of the journey are column B and the miles traveled are column D.

=SUMPRODUCT(--(A2:A100=LG52RYT),--(B2:B1000=NOT Sure???, D2:D1000)

Would I use the above formular? And what would i put in the section i have marked "not sure"?

View 10 Replies View Related

Convert Sumif To Sumproduct

Dec 8, 2010

I need to convert the following formula to a Sumproduct so the link will not be broken when I close the linked workbook:

=SUMIF('Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332,"M&R Parts D",'Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)

View 4 Replies View Related

More Than One Criteria In SUMIF Or SUMPRODUCT,

Jan 19, 2009

I need to add amounts in column B, based on column As account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.

View 9 Replies View Related

Sumif? Sumproduct? If? Vlookup

Jan 30, 2009

I have a daily tracking sheet. I want (off to the right) to be able to enter start/end dates and have it sum the total grossage for JUST those dates alone. Which function do I use?

http://s401.photobucket.com/albums/pp94/nmweir/?action=view&current=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">

direct link? :
http://i401.photobucket.com/albums/p...r/untitled.jpg

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved