Sumproduct By Reference Cell As Criteria
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 endusers.
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 (monthend 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.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
SUMPRODUCT With Cell Reference Rather Than Text
{=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 "Jan08" the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.
View Replies!
View Related
Vlookup:keep Cell Reference As Lookup Criteria
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 Replies!
View Related
Sumproduct With Two Criteria
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.
View Replies!
View Related
Sumproduct With Criteria
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 Replies!
View Related
How To Use SUMPRODUCT With Multiple Criteria
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.
View Replies!
View Related
Multiple Criteria  SUMPRODUCT
I'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.
View Replies!
View Related
Sumproduct With Blank Criteria
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
View Replies!
View Related
Sumproduct  Count More Than One Criteria By Date
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
View Replies!
View Related
Multiple Criteria Countif Or Sumproduct
I haven't been this deep into excel before. The deeper I look, the more potential I recognize, the more amazed I get. That being said, I have come to a tough count issue. Let me attempt to explain as precisely as possible. My current worksheet is large but I am only particularly concerned with two columns of information (Regions) and (Days). The logic I am attempting is something along the lines of Count If Region = East, or West, and Days is greater than 0, less than 60. I am open to any and all suggestions on how to tackle this situation. I have been able to achieve similar counts by using pivot tables but the dynamic nature of these two columns presents some difficulties that my “new user” mind has been unable to work through.
View Replies!
View Related
Three Matching Criteria Of Data, Is Sumproduct Appropriate?
I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time. So I need a formula or process (taking and comparing the data from both sheets) to do the following IF 1. The dates of a widget sale and the accessory sale match (in both sheets) 2. The salesman ID in both sheets match 3. The names of the customer in both sheets match THEN 1. Add up the total dollars by salesman for sales that match the above criteria I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .
View Replies!
View Related
2 Criteria Sumif Or Sumproduct With Wildcards
I have 3 relevant columns, A, B and C with 999 rows Column A is a status indicator "Yes" or "No" are the only options Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*" Column C is the data range that needs to be summed Right now i have this: =SUMPRODUCT((A2:A999="No"),(B2:B999="*VERIZON*"),C2:C999) But, it seems sumproduct does not allow wildcard matches within it's "" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.
View Replies!
View Related
SUMPRODUCT With Today() And Text Criteria
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 Replies!
View Related
Sumproduct By Last (criteria) Month With Data
I have 3 or 4 criteria and one data range to sum based on these criteria. If I remove date as a criteria, I can make a sumproduct formula work, and even if I include a specific month it works as well, I would like to write a formula that uses the most recent month with data as a criteria. My reasons are that some banks pay our reps monthly, some quarterly, some semiannually, and some whenever they damn feel like it. I need to take a snapshot of what each rep has invested in each bank: So for Bank A his last Statement came in March, but Bank B sent a statement in January. Here's what I have so far: =SUMPRODUCT(Month('2007 Data'!$F4:F$97),'2007 Data'!$G$4:$G$97=Sheet1!$A2,'2007 Data'!$B$4:$B$97=Sheet1!$G$1,'2007 Data'!$H$4:$H$97)/(month('2007 Data'!$F$4:$F$97) I know that the Month function here is incorrect but I'm going bonkers trying to wrap my head around this. FYI, 2007 Data is my Data page, Col F = Date Range, Col G = the Rep ID, Col B, = the Bank name, Col H = Assets Invested; Sheet1 = my results page. I know, I KNOW, many of you will scratch your heads and say this idiot should just use a Pivot table, I know. I have. Let's just say some of the endusers are not comfortable with Pivot Tables. I've come up with a simple listbox/command button to populate the axes of the chart (Column A = Reps, Row 1 = Banks)and I need this formula inside the chart. After seeing and learning from many of the posts on this site, I'm sure someone knows how to acheive what I'm after, I think it's just a matter of my communicating it as well as I can.
View Replies!
View Related
Lookup With Multiple Criteria...sumproduct
Attached is my sample workbook. There would normally be 600+ employees with multiple rows per employee. I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E. I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong. The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans  I have multiple rows with different plans and I need it to pull in all the data.
View Replies!
View Related
Sumproduct With 2 Criteria & Wildcard
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 Replies!
View Related
Sumproduct Of 2 Criteria In A Single Array
I want to use Sumproduct function to sum up the values that belong only to Product "PXT" and "PCT". I enter it as array but my formulae doesnt work. can someone give me a hand. Here is my formulae: =SUMPRODUCT((C2:C10="PXT")*(C2:C10="PCT"))*(A2:A10) ABCD 1ValueQtyProduct 2299.944PXT 3186.53PXT 4711.071PCA 5561.862PCT 6608.961PXT 7520.026PCT 8427.682PCA 9397.341PCA 10387.664PCT
View Replies!
View Related
SUMPRODUCT Function With Date Calculation Criteria
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date  purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below. One cell should contain the aggregate security purchase amount whose term is 190 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
View Replies!
View Related
Multiple Criteria Met In A Sumproduct Formula.
I have 2 columns of data being populated by vlookups Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example. If column H has a text entry then don't count it. If column H has a number less than zero then don't count it. If column H has a number greater than zero but column I is blank then don't count it. If column H has a number greater than 0 and column I has a text entry then count it. I've tried using many variations of a sumproduct formula and none of them work. This formula counts all instances where column I has a text entry without checking column H for a number greater than 0. =SUMPRODUCT((H2:H110>0),(I2:I110<>" ")) Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this.. =SUMPRODUCT((H2:H110>0&<>"*"),(I2:I110<>" ")) but this causes an error in the formula somehow that I can't figure out. I even tried this =SUMPRODUCT((H2:H110>0&"*"),(I2:I110<>" ")) and I get a formula that counts only the times text appears in column H and column I together which is not what I want either. I'm selftaught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.
View Replies!
View Related
Sumproduct (add A 3rd Column Of Criteria)
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.
View Replies!
View Related
Sumproduct Formula Needs To Halve A Value Depending On Criteria
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in service orders tab. BUT.....If two salesman pair up on up on a job then the formula doesn't recognize the joint name. eg Scott/Ash in row 21 & 22 (Service orders). I need the totals to half the job and add it to the salesmans total accordingly.
View Replies!
View Related
SUMPRODUCT With 2 Criteria, But One Is A Maximum Number Of Items To Include
I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not allinclusive. Column B  Lots of numbers, the SUM range Column C  Yes and No flags. Y or N C1 = maximum number of matching items to include. So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.
View Replies!
View Related
SUMPRODUCT With Multiple Criteria: Count The Number Of Documents
I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column. I have tried using this SUMPRODUCT formula: =SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones"))) but it is generating incorrect values! Specifically:  Jones shoulld return 1  Franks should return 3  Smith shoudl return 0
View Replies!
View Related
2003: COUNTIF/SUMPRODUCT, Multiple Criteria W/Wildcard
I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('InvoiceDetail'!J2:J50="NewJob_Post.NET")*('InvoiceDetail'!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 Replies!
View Related
Countif Criteria With Named Reference
I'm trying to use a countif formula with a named reference but the formula is not calculated correctly. My example would be: countif(A1:A5,<max) with max being the named reference. I also tried various options using quotes around the named reference (ex. "<max") but that doesn't work either
View Replies!
View Related
Cell Reference :: Formula To Reference New Cells
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them. EXAMPLE: 12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View Replies!
View Related
Cell Reference Able To Reference Two Cells To The Left Even As More Cells Are Added
I have 5 columns set up: A,B,C,D,E D is the sum of A and B E is the sum of A,B,and C As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C. I also need E (which has also shifted over one) to sum A,B,C, and C2. Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
View Replies!
View Related
Last Cell Of A Row In SumProduct
How do i change the formula, =SUMPRODUCT(c14:au14,c15:au15) to include more columns added to row 14 and 15 in future. Since the column count will increase , the sumproduct has to be like =SUMPRODUCT(c14:"last cell in 14th row", c15:"last cell in 15th row")
View Replies!
View Related
Add SUMPRODUCT To Cell With Macro
I am trying to use the sumproduct function with ranges defined in my VBA code. I can’t seem to get it to work. I have two spreadsheets in the enclosed work book – T1, T2 T2 has four columns with the first three columns are text and the fourth column is an integer. The rows in this sheet may be repeated. T1 has four columns similar to the first three columns in T2. However, the rows are unique. I am trying to do a sumproduct for obtaining the counts on the fourth column in sheet T1 To obtain the values for the fourth column (for each row) in T1 I do the following ...
View Replies!
View Related
Counting A Number In A Cell When Sumproduct
I have a spreadsheet with 3 coloumns. In A I enter Address, B Date, C Ward/District Area (this is a data validation list). At present I use; = SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2)). Summary B1 and B2 are dates to search between e.g 1/01/07 and 31/01/07. The above formula only counts the date enteries between B1 and B2. I would like to change this so that it still checks that the date still falls between B1 and B2 but is adds together the numbers entered in coloum A. So coloumn A would become number of completed actions not an address.
View Replies!
View Related
Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It
I have 2 problems relating to LOOKUP. Not sure if Excel can perform these calculations as they could get to complex. Problem 1 Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example: Tab 1 (Never changes) AB Bob1 Jon2 Fred3 Tab 2 (Dynamic, changes each week) AB Jon Fred Bob So it would work as follows. Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result. Problem 2 Weekly league rank table that shows position movements week by week Example. Week1 1Jon 2Bob 3Fred Week2 1FredUp 2 2BobNot Moved 3JonDown 2 Can Excel calculate/show the actual movements of league positions?
View Replies!
View Related
Sumproduct Too Slow, Delete Seconds From Every Cell
I have 2 problems: 1) I am doing multiple sumproducts on 6 sheets within a workbook with last row now being in the 12,000 cell range. Since putting in this SumProduct, my excel is extremley slow and affecting other windows applications. Is there anything I can use to replace this sum product ? =SUMPRODUCT((TEXT($B$4:$B$9003,"mmddyyhh")=TEXT(P2273,"mmddyyhh")),$D$4:$D$9003) It seems to be starting the calculations even before I put any values in column D ? I have already tried the manual calculation, but it is not making much of a difference. 2) After doing the sumproduct, I then need another row to look at the value of the sumproduct cell and report it in another sheet. My problem is that the cell contains the date and time in 1 cell, but in order to get the cell to lookup the value properly I need to delete the 00 in the seconds to refresh the cell or else it gives me back an n/a# value, even though the seconds are 00 and not 01 or 02 etc ? example : ....
View Replies!
View Related
Sumproduct Based On Adjacent Cell ID Code
I am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code. For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells. Column C would have dollar amounts. Sumproduct based on adjacent cell ID Code
View Replies!
View Related
Reference Cell In Macro Using Row & Column Numbers From Cell
I'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location): D2 is 17 (the row) B4 is 2 (the column) therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!
View Replies!
View Related
Modifying A Sumproduct Formula To Search For Words In A Cell
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 Replies!
View Related
