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
ADVERTISEMENT
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
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
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
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
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
Feb 2, 2010
Wildcard characters okay in countdiff and sumproduct? .......
View 9 Replies
View Related
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
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
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
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
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
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
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
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
Apr 19, 2014
I'm trying to sum up the total of a list excluding a list like this:
Column B
012*
019*
1758*
70228
70229
70230
70241
In my worksheet 1, it contains e.g.
Column AC
012-something
012-text
019-jumbo
016-things
70228
70221
Column W contains the numbers I want to add up:
$15
$12
$10
$19
$15
$11
My current formula is:
=SUMPRODUCT(--ISNA(MATCH(Sheet1!AC$4:$AC$157,B2:B7,0)),Sheet1!W4:W157)
It has managed to exclude the numbers (e.g. 70228, 70229, etc) but not the text strings (e.g. 012*) when summing it up
View 2 Replies
View Related
Jun 19, 2014
Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.
Here is the set up:
Column AColumn BColumn C
(Side)(Qty)(Price)
Buy5,51215.67
Sell119,428null
Buy24,20945.77
Sell20,05412.25
...
I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))
The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.
View 5 Replies
View Related
Feb 21, 2009
Have a cell that says something like "123 main street".
I want the cell next to it to = Main if it detects main in the other cell and if not then it should say no. I tried this.
=if(B2="*main","Main","No"
However, it is not working.
View 9 Replies
View Related
Jun 7, 2006
I transferring data from the mainframe and some data has ????. I can not replace them with something else because excel think it is wildcard. The reason for this request is I was trying to count the number of cells that has ???? and the count was something different each time. If you want to have fun try it out. It will count ?, ??, ??? but not ????.However my question would be how to change this (????) to some thing like unknown...
For Each rngC In rngJbn
rngC = Trim(rngC)
If rngC.Value <> newvalue And rngC.Value <> "ab-" Then
'MsgBox "****" & rngC & "***"
Résultat = Application.CountIf(Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible), rngC)
MsgBox rngC & " " & Résultat & " " & i
End If
newvalue = rngC.Value
Next
View 6 Replies
View Related
Nov 13, 2008
Is it possible to use the wildcard in an IF formula? For instance, I have a spreadsheet with text and numbers in the cells, the number of characters varies in the cells so a left, right or mid is out. I want the formula to look to the cell and if any part of the text contains "ABC" then I want to return 1. =IF(C18="*ABC*",1,"") Cell C18 containts the letters ABC in order but my formula is still returning blanks?
View 4 Replies
View Related
