Sumproduct With Text String (wildcard)

Aug 19, 2008

I am having problem filtering out criteria by putting together a sumproduct formula. I would want to use wildcard to populate 2 criteria from the data sheet, they are "Ducted Evaporative Cooler Single Stage 1 Tier" and "Ducted Evaporative Cooler Single Stage X-1 Tier". In my formula, I have used a wildcard "Ducted Evaporative * 1 Tier*" for the prior and "Ducted Evaporative * X-1 Tier*" for the later. I would like to know whether by checking my formula that i am using....

View 9 Replies


ADVERTISEMENT

Wildcard For Text In Sumproduct

Aug 14, 2008

Tring to get the following spread sheet to calc column C using two criteria
Criteria 1 Column D must contain HC Investor anywhere
Criteria 2 Column A must be less than Reimbrusement - 11
The following formula in G2 appears to have an error in criteria 1 ....

View 9 Replies View Related

Text Wildcard In Sumproduct

Jun 15, 2009

I am having great difficulties in finishing this formula off.
It is a sumproduct containg many criterias, however one of them is to only add up a certain coloumn if another column begins with L&L...

The coloumd contains L&L and a number, but the number changes so i need the number to be a wildcard. I have tried ="L&L???" but this does not work.

=-SUMPRODUCT((Catagory="STK Retail")*(MONTH(Inv_Date)=3)*(YEAR(Inv_Date)=2008)*(How"Account")*(Order="L&L???")*(Stock))

View 9 Replies View Related

SUMPRODUCT With 2 Wildcard Text Match?

Feb 20, 2014

Here is what I have so far:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56,L9:L56)/SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56)

So in column A is the text string i.e Big Red Chiquita Apple
Column B is the dollar value
Column C is another variable.

If I want to find "Red" and "Apple" ONLY within same cell. What would be my best bet?

View 5 Replies View Related

Sumproduct With Text String

Aug 4, 2008

I have trying to write a sumproduct formula, however, the total sum amount that i get is "0", which i think the formula is not picking up the text.

=SUMPRODUCT(($Y$2:$Y$65536="Evaporative cooler*")*($Q$2:$Q$65536=$AM$5)*($N$2:$N$65536=6),($AA$2:$AA$65536))

Y column is the text column to find the Evaporative cooler, the Q is another column where i have write the text out at AM5, N is a number field. The only bracket that is not working is the Y. Can you please let me know if I did anything wrong to it?

There are 3 types of Evaporative cooler, 1) Evaporative cooler - phoenix, 2) Evaporative cooler - Cal, 3) Evaporative cooler - Brisa. I wondered if I can use "*" to select all of the Evaporative cooler, instead of listing all three.

View 14 Replies View Related

Sumproduct With 2 Criteria & Wildcard

Jun 8, 2007

I have spent the last 2 1/2 hours trying to figure this one out on my own. I have attempted multiple possiblities from the forum, however without success.

Okay, I need a count based on 2 criteria. My data source is a different workbook.

My last attempt on a formula is:

=SUMPRODUCT(('[eTE Status - TX HSP Math 2009.xls]Project'!$B$2:$B$248=D2)*('[eTE Status - TX HSP Math 2009.xls]Project'!$A$2:$A$248="978*"))

Column A - ISBN #
This will ALWAYS begin with 978 or will contain N/A. There are no blank cells in this column
Column B - Grade

In the formula above, cell D2 represents the Grade criteria. And in the second part of the criteria I was trying to create a wildcard so all numbers beginning with 978. Excel "accepts" this formula with giving me an error message, but yields 0 as a result rather than 34.

View 6 Replies View Related

Sumproduct - Select All Values Per Wildcard

Jul 30, 2008

Sumproduct formula with selection criteria of "A", "B"... in the first column and numeric values in the next colum. The selection is controled by a List where the user can choose "A", "B", ... ,or "ALL". What wildcard-type (pseudo) is needed to select all values when "ALL" is chosen?

I'm using Sumproduct because there is other selection criteria, but it should not impact this part of the formula.

Example: Sumproduct((A1:A100=X1)*(B1:B100)) , where A=selection aray, B=numeric value, X1=corresponding list selection to A

View 9 Replies View Related

Wildcard Characters Okay In Countdiff And Sumproduct?

Feb 2, 2010

Wildcard characters okay in countdiff and sumproduct? .......

View 9 Replies View Related

Validation Type Drop-down Wildcard In Sumproduct

Oct 5, 2009

I have a worksheet report that counts question answers filtered by facility, date range and case manager. I use Data Validation "List" criteria to make the in-cell drop-down lists for the criteria of the variable values. For most of them the selected value works fine, but for the case manager drop-down I need a value in the list, other than the peoples names, to represent "All" or be a wildcard and include all values in the selection. Here is the formula:

View 4 Replies View Related

2003: COUNTIF/SUMPRODUCT, Multiple Criteria W/Wildcard

Nov 24, 2008

I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?

View 3 Replies View Related

Extract From String Using Wildcard ..

May 14, 2009

I've this question, how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.

Below is a example which i create for better understanding.

View 11 Replies View Related

Extract From String Using Wildcard

May 14, 2009

how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.

Below is a example which i create for better understanding.

ExtractSimple.xlsx
http://www.yousendit.com/download/dVlveUNDSWU3N0RIRGc9PQ

I don't know if this break the rules, Please kinda forgive if i do as it quite urgent. I'll delete if it don't comply with the breaks.

View 9 Replies View Related

Search Text Cell For Wildcard Match

Feb 27, 2007

I am looking for a way, to search a cell that has a no more than 4 words sperated by a space, and if a match display it.

I had something like this:

IF(Search=LocationList,"true","False"

So the Locationlist is a list of locations that is brought from another sheet with this:

=Addresses!A3&" "&Addresses!B3

and i need to search the cells with in LocationList (about 4000 cells) for a match... is this possbile ?

View 9 Replies View Related

Sumproduct Formula To Exclude String Values

Jan 22, 2014

Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF

I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.

Below is the entire forumla just in case.

11111111FT04comp
comp
tri
11comp
11tri
tri11111111FRP06

=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)
*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))

View 6 Replies View Related

Evaluate Formula Derived Empty String In SUMPRODUCT?

Dec 10, 2013

I've been trying to get this SumProduct formula to return anything except an error for a while now.

=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17

View 2 Replies View Related

If Text Found In String Return String

Jan 31, 2008

Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View 3 Replies View Related

Sumproduct With Value Or Text

Nov 27, 2013

My formula works fine:

In cell D12:
=SUMPRODUCT(('Raw data'!$L$25:$L$10958=Sheet1!$C$9)*('Raw data'!$C$25:$C$10958=Sheet1!$C12),('Raw data'!$H$25:$H$10958))

Sheet1!$C$9 = "Both"

But when i change C9 to i.e. 2013 or 2012....the data does not show anything only 0s?

i have attached a sample : sumprod_values_issue .xlsm‎

View 11 Replies View Related

Sumproduct & Text

Feb 5, 2007

I am trying to use the following formula:
=SUMPRODUCT('Raw Data'!$B$2:$B$10000=$A$1)

The reason why I am using sumproduct is b/c I have to add more to this formula. In column B on the Raw Data sheet there are years listed. The years are formatted as text. Is there a way to have this formula work without having to change the format of column B?

View 10 Replies View Related

Sumproduct For Numbers Within Text

Jan 16, 2014

How to use Sumproduct for the following?

Col1 Col2
5 1 mL
4 1.5 mL
5 2.5 mL
7 2 mL
2 1.5 mL

View 7 Replies View Related

Sumproduct To Find Text

Nov 18, 2008

I recently had a lot of help using the sumproduct formula with isnumber datevalue added to it. I now want to do the same but rather than finding numbers, i want it to find text.

In my attached example. You can see that Jerry A has received three applciations, but 2 of them have been cancelled. How can i add the formula where it would only count Jerry A's applications, but where they have been cancelled i.e. the formula would show 2 not 3.

View 6 Replies View Related

Sumproduct #Value! Error Due To Text

Feb 26, 2007

I'm using this sumproduct and it works until a cell has text.
=SUMPRODUCT(($C7:$C16=$C$2)*($D7:F16))

I've tried =SUMPRODUCT(--($C7:$C16=$C$2),($D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.

View 9 Replies View Related

SUMPRODUCT Contains Text, Using Asterisk

Nov 9, 2009

Let's say I have two columns: Homeless (column A) & Services (column B). Homeless responses can be either "Y" or "N". Service can be "a", "b", or "ab". Here is an example:

Homeless Service
y a
y b
n a
y ab

I was hoping to use SUMPRODUCT to calculate clients who are homeless and receiving "a" service. I want to be able to calculate those receiving "a" service even if they are receiving "b" service, so I attempted to use asterisks like I have used prior with the COUNTIF function. Here is what I thought would work:
=SUMPRODUCT((A2:A5="y")*(B2:B5="*a*"))

And I expected it to return 2, but I found that I couldn't get the SUMPRODUCT to work with asterisks. I am able to get the results I want be creating a new column to sort the services into just those who are homeless and then using COUNTIF, but I'd rather come up with a more streamlined approach.

View 3 Replies View Related

Sumproduct To Return TEXT Value?

Dec 23, 2013

I written the formula below:

=SUMPRODUCT((B7:B9=$A1)*(E7:E9>=0),"CAP EXCEEDED","CAP Not breached")

B7:B9 = text and numerical values in format Year 1, Year 2 etc
E7: E9 = numerical values

what I am trying to achieve is if YEAR 1 for example is equal to cell A1 and the value in column E is greater than 0 then return statement "CAP Exceeded else return the statement CAP not breached.

View 6 Replies View Related

SUMPRODUCT To Return Text

May 1, 2008

The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....

View 9 Replies View Related

Sumproduct Using Text And Values

Jan 21, 2004

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.

View 9 Replies View Related

SUMPRODUCT Use To Return A Text

May 11, 2006

If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value?

COL ACOL BCOL C
A1X
A2F
A3G
B1E
B2S

I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F"
=SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).

View 9 Replies View Related

Extract Text From Given Point In Text String When Data Points Do Not Share Given

Jul 9, 2014

I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.

The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].

The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?

View 3 Replies View Related

Search Cell For Text String And Paste Text In Based On Finding?

Jun 29, 2014

I need to create a macro to do the following:

Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.

For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.

View 7 Replies View Related

Match Row That Contains Text Text String; List Associated Values From Pivot Table

Mar 28, 2009

refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.

View 3 Replies View Related

Excel 2011 :: Find Multiple Text Strings In Another Text String

Mar 8, 2013

How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.

A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.

After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.

New words will be added to the sets of words continually as required.

Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.

I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.

The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.

If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.

Happy to consider a different solution if the task can be done better a different way.

Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.

View 1 Replies View Related







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