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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 29, 2013
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?
View 8 Replies
View Related
Apr 8, 2014
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
View 2 Replies
View Related
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 Replies
View Related
Sep 4, 2009
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
View 9 Replies
View Related
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
Apr 24, 2009
I'm trying to work out how to fix the formula below to take into account and ignore and text entries, while giving me the result of the sum of column K minus the sum of column J. If I delete the text entries, the code works but I need the text entries to stay where they are. I've attached a sample sheet with fake info to explain whan I'm trying to do.
Cell N28 on the 'MGMT INFO' tab contains the following formula:
=IF(ISERROR(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))),0,(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))))
Columns J and K on the 'Sheet01' tab contain the Pay and Invoice information for all the work planners for that client that I'm trying to find the difference between. Each work planner has 'Pay' and 'Invoice' also in that column though, one entry per planner which is causing the SUMPRODUCT formula to screw up.
View 3 Replies
View Related
Feb 19, 2010
Im attempting to add number of dates within a column (B). I have 2 criteria: Today and person "William". Im using the following and return results is "0"
A: Name
B: Date
C: Type
=SUMPRODUCT(--(Data!$B$2:$B$3100=TODAY()),--(Data!$A$2:$A$3100="William"),--(Data!$B$2:$B$3100))
Once I get this working I would like to adjust for last 30days and look at Type (C) = New
View 5 Replies
View Related
Oct 16, 2007
I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same row as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working b/c it wont take TEXT. Plus I want it to search across all of the columns, not just one.
Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) :
=SUMPRODUCT((Sheet2!B1:B6500=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500))
Is there someway to fix this, it would be a huge time saver.
--------------------------------------
1Q08
Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge ....................
View 4 Replies
View Related
Sep 2, 2008
{=SUM(IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUM(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2: F$188))+SUM(IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUM(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207 '!F$2:F$188))+SUM(IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUM(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6) ,'AS21'!F$2:F$188))}
Failed to convert using
=SUMPRODUCT(--((IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUMPRODUCT(--IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUMPRODUCT(--((IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188)))))))))
A6 is a month column and looks like this "Jan-08"
the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.
View 9 Replies
View Related
