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

## SUMPRODUCT Criteria Via Cell Reference?

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.

## Sumproduct On Cell Reference?

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

## Sumproduct Cell Reference Not Working

Jun 18, 2009

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

## SUMPRODUCT With Cell Reference Rather Than Text

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.

## Averageifs Using Cell Reference In Criteria

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.

## Vlookup:keep Cell Reference As Lookup Criteria

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.

## Macro Used Textbox In Worksheet Rather Than Cell Reference As Criteria To Find Matches

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.

## SUMPRODUCT Array Reference Causing #N/A

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

## Sumproduct Formula With Date Reference Not Working

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

## SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

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

## Sumproduct With Two Criteria

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.

## Sumproduct With Criteria

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?

## SUMPRODUCT To Match Criteria

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

## Sumproduct With Multiple Criteria?

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

## Sumproduct Meeting Two Criteria?

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.

## Sumproduct Using Name Range As Criteria?

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

## How To Use SUMPRODUCT With Multiple Criteria

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.

## How To Use Sumproduct With Multiple Criteria

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

## Sumproduct (add A 3rd Column Of Criteria)

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.

## Sumproduct With Two Exclusion Criteria

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)

## Sumproduct With Multiple Criteria?

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

## Sumproduct With Blank Criteria

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

## More Than One Criteria In SUMIF Or SUMPRODUCT,

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.

## Multiple Criteria - SUMPRODUCT

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.

## Sumproduct With Multiple Criteria

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]

## Sumproduct With 2 Criteria & Wildcard

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

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.

## Sumproduct Formula Based On Criteria

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

## Adding Third Criteria To Sumproduct Function

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

## Sumproduct - Count More Than One Criteria By Date

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.

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