# Sumproduct By Reference Cell As Criteria

Jun 5, 2007
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.

View 4 Replies
ADVERTISEMENT
Apr 12, 2006

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.

View 9 Replies
View Related
Mar 23, 2012

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"))

View 8 Replies
View Related
Jun 18, 2009

I have the following line of code in the code for a textbox in a userform:

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
Nov 21, 2013

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.

View 7 Replies
View Related
Jan 7, 2010

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 Related
Sep 26, 2013

This 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.

View 8 Replies
View Related
May 27, 2009

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 Related
Mar 7, 2012

I 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

View 7 Replies
View Related
Apr 4, 2014

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]....

View 3 Replies
View Related
Feb 9, 2006

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 9 Replies
View Related
Jan 24, 2007

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 Related
Jun 3, 2014

I 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

View 2 Replies
View Related
Jan 21, 2014

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 Related
Jun 20, 2014

I'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 Related
Jul 15, 2014

Looking 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

View 4 Replies
View Related
Jul 17, 2009

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 10 Replies
View Related
Mar 10, 2013

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

View 7 Replies
View Related
Oct 26, 2009

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 3 Replies
View Related
Jan 9, 2012

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)

View 9 Replies
View Related
Feb 22, 2012

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))

View 1 Replies
View Related
Oct 30, 2007

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 9 Replies
View Related
Jan 19, 2009

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 Related
Jan 30, 2009

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 9 Replies
View Related
Apr 28, 2009

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]

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
Dec 26, 2013

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

View 5 Replies
View Related
Feb 5, 2014

=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)),"")

View 3 Replies
View Related
May 19, 2008

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 14 Replies
View Related
Jan 30, 2009

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 4 Replies
View Related
Sep 16, 2009

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 9 Replies
View Related
Oct 1, 2009

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 self-taught 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 8 Replies
View Related
Jan 26, 2010

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 2 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 15, 2008

Hmmmm. Looks like I ruined my original posting while trying to mark it solved.

View 14 Replies
View Related
Feb 15, 2012

I have set up a formula to count the occurrences of multiple conditions in a table of data (see below..) the formula reads from 4 cells of criteria but I will not always use the 4 criteria and maybe only need to use say 2 criteria - when I do this my formula doesn't work - my example below shows this... How do I make the formula continue to work when some criteria cells don't have any entries?

This table is a simple example but my main work will have a much larger table (poss. several thousand rows) and probably about 10 criteria cells, so running a big formula with 10 nested IF statements will be too cumbersome... any smart workround for this within the SUMPRODUCT formula?

The top left cell (Fruit) is A1..

FruitColoursTownsNamesApplesRedLondonHarryApplesGreenParisBertPearsBlueLondonFred

PlumsRedVeniceSteveApplesRedLondonBertCriteriaResultFruitPears0ColoursTownsLondonNamesFred

FruitColoursTownsNamesApplesRedLondonHarryApplesGreenParisBertPearsBlueLondonFred

PlumsRedVeniceSteveApplesRedLondonBertCriteriaResultFruitPears0ColoursTownsLondonNamesFred

The formula currently in my Result cell is

=SUMPRODUCT((A2:A6=B10)*(B2:B6=B11)*(C2:C6=B12)*(D2:D6=B13))

which returns 0 and should be 1 in this example...

View 2 Replies
View Related
Sep 24, 2012

I have two sets of criteria I want to incorporate into one formula. In the first column, if the criteria is matched, it will check the criteria in the next column. The criteria in the second column is something that resembles the 'or' function. So if criteria equals x,y, or z, sum the results from the data range c3:c98. I tried writing it like this.

=sumproduct(--(a3:a98=a),--(b3:b98=x(or(b3:b98=y,b3:b98=z))),c3:c98)

View 1 Replies
View Related
Mar 1, 2013

Is there a simple way to include the values of an entire range?

This is what I tried, but I get #name

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = Range("C3:AA3"))*Datasheet!F2:F65000)

If I use a single cell it works, but only with what matches that cell.

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = C3)*Datasheet!F2:F65000)

I'd like the criteria to match anything in that range C3:AA3. Do I need to specify each cell individually or is there an easy way have it use the entire range's values for its criteria?

View 9 Replies
View Related
Sep 5, 2008

I'm after a SUMPRODUCT that will give me the number of times that F is in column B2:B1000 when non of the following is in column A2:A1000.

000

001

002

003

004

005

006

All the above are in text format.

Example below would return 3.......

View 9 Replies
View Related