SUMIF allows one to enter a [valid] criteria text string into cell D2, say
">14000", and then reference D2 as the function criteria, e.g.,
=SUMIF(B5:B10,D2,C5:C10).
Is there a syntax that SUMPRODUCT will accept that does the same thing? Note the inclusion of a comparison operator is important.
In reference to an earlier thread, which shg and ByTheCringe2 both were genereous in their assistance, I would like to add a wrinkle, as requested by the end-users.
how to reference an earlier thread for people to reference but you can search " Sumproduct By Last (criteria) Month With Data". Edit by ByTheCringe2: Sumproduct By Last (criteria) Month With Data.
We had used the MAX function to find the most recent investments, per counselor, per bank. I now need to find the most recent investments as of a certain date (month-end perhaps). I have ideas, but I'm afraid my misperceptions/assumptions might hinder me from seeing all available options. Initially, I thought I could use a text or listbox on the summary sheet to enter a date that would link to the reference cell on the data page, and this reference cell would then be used in whichever formula I came up with.
I have formula but would like to change the ="03-01-2011" to reference what is in a cell =a2, etc.
=SUMPRODUCT(--(LEFT($B$2:$B$3582,10)="03-01-2011"))
I have the following line of code in the code for a textbox in a userform:
View 4 Replies View Related{=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.
I'm trying to use AVERAGEIFS to take the average of the values in a column that are > a low cutoff value and < a high cutoff value.
In my sheet, the cutoff values are calculated in two cells, and I need to refer to those values in my AVERAGEIFS formula.
Here's what I've tried so far:
=AVERAGEIFS(H2:H81,H2:H81,">B10",H2:H81,"<B11")
This gives me a #DIV/0! error.
If I replace the cell references in the criteria clauses (">B10" and "<B11") I get the correct answer, so I guess it's not handling these sections correctly.
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
View 2 Replies View RelatedThis is a continuation of thread 182629 [URL] ....
The End User wanted originally to type characters into cell C1 of a worksheet that the macro searched for in another worksheet, copying across every row containing the input.
The responses gave me the following code which worked perfectly:
VB:
With Sheets("PICKLIST")
myFind = "*" & .[c1].Value & "*"
End With
If myFind = "*" & "" & "*" Then Exit Sub
Unfortunately, the user NOW wants a Text Box instead of putting the characters into a cell.
I added a textbox (originally called 'TextBox2' but renamed "Choice") and "Dimed" CHOICE As Range.
Then tried the following code:
VB:
With Sheets("PICKLIST")
myFind = "*" & CHOICE & "*"
End With
But it generates a "RunTime Error 91" - Object variable or With block variable not set.
Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...
View 2 Replies View RelatedI have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).
I have 52 colums ie one for each week.
=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000
Basically I have an equation:
=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))
Where the user inputs the number of rows used in A8.
The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
[code]....
I should be able to do this by now but I keep getting an #NA error. I am
trying to use this sumproduct formula
=SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
am looking up store #"921" and Attribute "fb and sum where these two criteria
meet. The column I want to sum is AS7:As285. What am I doing wrong.
In Column A1:A20, these are included 5 types of data (red, orange, yellow, green, blue); in column B1:B20, these are included the total salary amount, and in column C1:C:20, these are included "Sole" , "Joint" or "Partnership", so if column A is included red/yellow/green (excluding orange and blue) and column C is included Sole/joint/Partenship, and total salary is between 6000 and 9000, then calculate the total salary of all the customers. How can I use sumproduct function to fix it?
View 8 Replies View RelatedI am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.
See the attached excel file : nozzle tester.xlsx
I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock
View 3 Replies View RelatedI'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
View 3 Replies View RelatedLooking to troubleshoot using a name range in a multiple criteria sumproduct formula.
For instance, this formulas works fine:
[Code]......
But, when I try to substitute the two-alpha codes as a defined name range ("keys"), it errors as "N/A":
[Code].....
An example workbook is attached : Example Workbook.xlsx
I am stuck - I have a large amount of data for a group of physicians I work for. I am trying to set up a monthly trend report to be able to run quickly after I plug in the data. I want to use some sort of lookup to look up two things - 1) the physician's specialty and 2) the month.
Can anyone look at the attached example and tell me how to do this? I have started a SUMPRODUCT formula, but am stuck on how to tell it to find only that month's data.
ID, Name, Point, Session
1111, Viking, 5, 1
2222, John, 6, 1
1111, Viking, 10, 2
What's the formula to get the Point cell value with criteria ID = "1111" and "Session = 2" ? In this case, it will return me the value of Point = 10
I have the following sumproduct formula that's providing solid results but I would like to add a 3rd column of criteria. I'v tired with little succes.
The following formula <=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*('IW 38 DUMP for Planning'!$E1:$E10000={"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))> totals all of the work in plant area "2A", in this case 52 records. I would like it to filter further with values in $H1:$H1000 matching criteria "CONTRACT", "MACH" OR "HTSMET".
The data is easy to find with pivot tables but I would like to take that manual step out of the reporting being doen from these records.
I am trying to get this sumproduct to work that will exclude two criteria:
1) If there is an error in the sum range (Y5:Y1000)
2) If right(H5,5)= D5& "Total"
This is the formula I have, but I'm not sure how to get the first criteria to work well, or how to get two exclusion criteria to work.
SUMPRODUCT(--($H$5:$H$1000=$D5&" Total"),--(ISERROR($Y$5:$Y$1000)),$Y$5:$Y$1000,$Q$5:$Q$1000)
I was wondering if I could use a range of cells as my criteria as opposed to inserting quotation marks with each criteria. For example in the syntax below can I do something like this? Report!$C$3:$C$5000=B45:B51?
=SUMPRODUCT((Report!$A$3:$A$5000="XXX")*(Report!$C$3:$C$5000=???)*(Report!$E$3:$E$5000))
I have the following sumproduct formula which looks in one column to search for a series of dates (greater than one date and less than another date). It then looks in column O to see if there are certain criteria if the date criteria is met. The problem is I have a lot of blank cells in column O which need to be captured.
I have this:
=SUMPRODUCT(--('Raw Data'!$B$2:$B$50000>=$H$2+0)*('Raw Data'!$B$2:$B$50000
I need to add amounts in column B, based on column A’s account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.
View 9 Replies View RelatedI'm trying to create a budget worksheet that pulls actual data from another sheet within the file for comparison (Budget vs. Actual). There are two criteria: 1) the actual transaction falls into the same category of transaction as the budget line item (e.g., mortgage payment) and 2) the date of the actual transaction matches the month in the budget (e.g., a January or March transaction isn't pulled into the actual data for February budget information). From there, I'd like it to sum any charges or reduce by any deposits for those given criteria.
I've tried numerous things from DSUM, to SUMIF with IF, to SUMPRODUCT.
I received an answer to my original question and now have a new question but I wanted to reference my original for the history. I posted my new question at the end of my original thread.
[url]
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.
I have a sumproduct formula based on some criteria, but I don't know how add another criteria wherein I need to exclude in the count if the date in column F is 1/1/2009
Attached excel file for reference. LE26dec.xlsx
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.
I tried to edit it myself with the function below but it didn't work.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I have a document with the following data
1: NAME
2: DESCRIPTION
3: DATE "12/15/2007" format
I would like to know if anyone knows how I can perform the following. And please excuse me for not writing this very technically I am a bit lost.
if name = "JOHN" and description = "APPLE" count how many apples there are for john.
Now it gets complicated for me because the dates are as follows. Lets say it starts 1/1/2004 the next cell might be 1/3/2204, 1/6/2204, etc all the way to 2008. I would like to find a way to say
If date = day/month/year I would like to count the whole month instead of the individual days. am I making sence? Let me give you another example.
record reads "," represent cells
JOHN,1/2/2204,APPLE
JOHN,1/3/2204,ORANGE
JOHN,2/3/2004,APPLE
I would like to be able to come up with the following
JOHN had 1 APPLE in JAN 2004
JOHN had 1 ORANGE in JAN 2004