Free Microsoft Excel 2013 Quick Reference

Sumproduct with offset?

Hello everyone,

I have a large data where I need to sum certain values based on the
criteria various rows above or below. For example, I need to sum the
values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in
column C).

2 3 2
1 1 3
4 3 6
1 1 4
2 5 5

I have tried to combine sumproduct with the offset function, but I just
can't get it to work. Thanks.


Post your answer or comment

comments powered by Disqus
The following formula is returning #REF, which seems to be due to the combination of the SUMPRODUCT and OFFSET formulas. Is there any reason why 2 OFFSETs can't return 2 ranges that SUMPRODUCT can evaluate?


The individual components all return values as follows:
Source E37 is a valid reference
Year(...) is actually zero in this column - so no offset of columns or rows from E37 in this case.
COUNTA (..) returns 3 - i.e. 3 rows high

Anyone have any idea on this one?


Can somebody tell me why the following formula doesn't work?


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
"Range1" is a named range that is 1r x 32 columns.

The intent of the formula is to count the number of entries in the particular row that match the correct entries that are listed in Range1. If no value has been entered in a cell in Range1, then I don't want that counted as a match.

It works if the Offset(...) is replaced with the actual range that the offset should refer to. However, I want the formula to be more general so that I can copy it.

Any ideas?

EDIT: I think I've narrowed it down to the ROW() part of the formula. I can't get it to work in any way when that is in the formula.

Dear All,

I am trying to use Sumproduct with Publishing day as criteria. My formula fails when I use Publishing day refer to column M and N. Please refer to attachment.

Kind Regards,


I'm doing a SUMPRODUCT with ranges A1:AE1 and A2:AE2. It works great when there are blank cells, unless that blank cell is generated by a formula... then I get a #VALUE! error.

See attached.

Any suggestions on a work-around?

In the attached spreadsheet, I need the highlighted cell to offset to column C if the input in B2 changes from "Owner" to any of the "leads." I have no idea how to do this.Ex.xlsx

I seek your help with the following VBA use of SUMPRODUCT with Variables and OR-ing:

My Goal: Using VBA with SUMPRODUCT and EVALUATE and Variables, write code that will do the following (as an example):

If (Col A='PGV' or Col A='OAK') and (Col B='2008') and (Col C='Real') then add Col D

Worksheet Details:


The results of the above equation applied to the worksheet would be 600, record 1 and 2 would add, but records 3 and 4 would fail the conditional statement (record 3 because Col A = 'BBB', and record 4 because Col B = '2007').

Working VBA:

The following VBA code works perfectly:

Answer = Evaluate("SUMPRODUCT(--(($A$3:$A$1000=""PGV"")+($A$3:$A$1000=""OAK"")), --($B$3:$B$1000=""2008""), --($C$3:$C$1000=""Real""), --($D$3:$D$1000))")


I need to make this code flexible to allow for multiple uses. If I replace the above code with variables, I have the following (this does not work):

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV"")+($A$3:$A$1000=""OAK""))"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")


If I remove - from Piece1 - the second conditional statement, the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV""))"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")


If I remove - from Piece1 - the second conditional statement, but keep the "+" operator (as an OR) the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV"")+1)"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")


I'm trying to use SUMPRODUCT with EVALUATE with VARIABLES and ORing.

I'm able to write working code with all of these pieces, except that the ORing statement is making for sleepless nights.

Can you help me with this?

Thank you

I'm trying to figure out how to use SUMPRODUCT with the OR operator. Here's my problem. I have a list of items that have effective dates and retirement dates; However, each item is not required to have a retirement date.

What I'm trying to do is search for items that have effective dates less than XX date AND a retirement date that is greater than XX date OR where the retirement date is blank. Here's the formula I'm using now:

= SUMPRODUCT(--(Effective_DateVALUE("1/1/1900")),--(Retirement_Date>VALUE("1/26/2008")*(Retirement_Date=" ")))

Im stuck on how to represent the retirement date portion to reflect that I want a date greater than 1/26/2008 OR where the retirement date is blank.

Any help would be greatly appreciated!

Hi All! Been trying to figure this out for hours, but can't do it. Hope you
can help solve this mystery for me!

First 2 questions:
1. Does SUMPRODUCT work with ADDRESS functions embedded within?
If no, then:
2. How can you combine SUMPRODUCT with a single condition? So with this,
here's the scenario:
Order # Qty 1 Qty 2
T1 1 2
T1 1 1
T1 3 1
T2 2 1
T2 1 1
T3 2 2
T3 1 2

So I want the SUMPRODUCT(Qty1,Qty2) for Order T1. The correct answer here
would be 6.

Hope you can help solve this!



I'm attempting to do a sumproduct with different dimensioned ranges. I've read multiple posts that say that all ranges in a sumproduct must have the same dimensions. I've tried different commands (index/match/lookup, etc) with no luck. Perhaps someone can come up with a solution for me.

Here is the data:
Sheet1: Sheet2:
Widget Parts Location 1 Widget Count Parts
A 4 A 2 20
B 1 C 6
C 2
Location 2 Widget Count Parts
B 3 3

What I'm looking for is the total number of parts at that location, so here the answer would be 20 (2 x 4)+(6 x 2) = 8 + 12 = 20 for location 1, and 3 (3 x 1) = 3 for location 2.

There are multiple locations and a lot more widgets than written here.

Any ideas anyone ???



Is there a way to create a macro from a macro with offsets to the cells. What i have is a program that has 4 identical areas ( they are estimates for a canopy) . The macro builds the canopy as you choose your products. There are 4 of these. I was wondering if there is a way that I can copy the macro and offset it 4 times instead of editing every single range.


First thank you for any and all the help I can get.
Can MAX be used with OFFSET and MATCH?
I'm trying to find the maximum value based upon MATCHing the value in
anothercell. I have these values (very simplified):
T600 11
T600 6
T600 15
T601 2
T601 12
The formulas I have tried are:
but end up with 11 for T600 and it should be 15.
Is there any way to do this?
Thank you in advance forthe help

I would like to use sumproduct with ranges of different sizes by padding the smaller array with zeros. Any suggestions?

For example, if the first array is always a fixed array with the following values:
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

and the second array can vary in length, with example values:
1, 2, 3

I would like a function that uses sumproduct to return the result: 116 (=0*10+0*11+0*12+...+1*18+2*19+3*20)

I think the real question here might be how to pad an array with a dynamic number of zeros...

Hi all,
I have a formula which returns the last/bottom value in a column (in this case column E on sheet 'Data Set'):
=LOOKUP(9.99999999999999E+307,'Data Set'!E:E)

Is it possible to have something to retrieve the value above it? (i.e. the last but one) - I thought I might be able to do it with OFFSETs but can't get it to work.



Dear Experts
How to write above line in (Refers to box under
Insert---> Name---> Define--->

When I use arrow key left or right then it jumps to some cells.

In Otherwords, how to define some name range with offset.

Please help

Hello forum,

I am new to the website but hope you can help me out. I am new to vb programing but have some limited programing in the past so I am picking it up pretty quick. Below is a example of what I am trying to use vlookup on:

Vehicles Type Name phone laptop printer
E201 1078 Mike 67567653 423423 k34234
E202 1078 John 234231 55234423 231244
E203 1152 Jados 55234423 423423 55234423
E204 1152 Cramer 234231 55 234423 231244
E206 1114 Napier 55234423 423423 55234423
E310 M88A1 Joe 234231 55234423 231244
E870 1078 Chon 55234423 423423 55234423
E871 1078 Dubbs 234231 55234423 231244
E873 1114 Rob 55234423 423423 55234423
E877 1078 Spann 234231 55234423 231244

This is a list of company vehicles and genric serial numbers for the particular people. I am using a 4 comboboxes on userform plus a ok button. Combobox 1 is the drop down for column A. Combobox 2-4 are for column C. My goal is to create a program that when I select a vehicle from combobox 1 and people from combobox 2-4, that it will write to cells in the formatt that I want and it will vlookup the serial number for those people. I have it working but I have a issue. My vlookup reference changes cells when I start the second vehicle since I offset formatt with a counter. This is how I would like it to look

1 E873 dubbs 234231 55234423 231244
Napier 55234423 423423 55234423
Cramer 55234423 423423 55234423
Chief 234231 55234423 231244

2 E873 dubbs 234231 55234423 231244
Napier 55234423 423423 55234423
Cramer 55234423 423423 55234423
Chief 234231 55234423 231244

3 E873 dubbs 234231 55234423 231244
Napier 55234423 423423 55234423

I end up getting N/A's because I dont know how to use cell with the name as the reference if its location is shifting every vehicle with offset. I have been trying to use a offset value with a counter to try and get what I want. Intitally it works but fails as the counter goes up. Any advise?

Hi all,

I have the code below (kindly provided by a genius from this forum) that
determines the active print range for a sheet. It works perfectly, however,
I wish to expand this returned range by 1 row. I have experimented with
offset but so far have had no success. Any ideas would be welcomed!

With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = ActiveSheet.Range("A1:Q" &
End With

Function LastRowRange(sh As Worksheet)
'This function determines the active print range for a list and returns a
range object.

On Error Resume Next
LastRowRange = sh.Range("A:A").Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
On Error GoTo 0

End Function

Hi there-
I am using the MATCH function to return a value from one sheet to another. I am trying to pair it with OFFSET to get the value in the right. Cells I5:M5 are merged and hold the MATCH text, Cells N5:O5 hold the data I want to pull back. Here's what I've got:

OFFSET((MATCH(G34,'Project 009'!I5,0)),1,6,1,7)

It throughs the error with formula, but, even when it didn't error out, it doesn't work. The MATCH is clean, and, returns the value of 1. I've tried the OFFSET both merged & unmerged.



Can someone assist me with a formula to sumproduct with wildcards, please?


I've got this but it doesn't work!!


Dear Friends,

I am facing a problem with OFFSET function. The following formula is entered to define a named range (CNTENGLISH).

And this defined name is used in formulas to do some calculations.  Of course it is working nicely.

But, when the few rows are deleted in the referred sheet, then the result in other sheets shows #NA error. Actually what happens with the named range is that it is not selecting the values properly.
I don't understand why? & what is the problem?

Any help? Thanks in advance.


Hi i need to some help with sumproduct. what i need is a way to use sumproduct with a fuction that contains a word. this is an example but doesn't work. =SUMPRODUCT(B2:B17,--($A2:$A17= contains "john")).
my problem is i have a sheet with names on it and some times people enter whole names and some times they dont. i need a way to sum up a number even if the name is "John S." or "S, John". Thanks

Thanks for your help that worked.

Hello VBA guru's!!! I have a question regarding find cut and offset...

I'm trying to use Find to search for a word (Total), using xlpart and then moving or cutting the entire cell contents over one column. I'm using the below code but I'm not sure how the syntax would go here when using the cut.desination(offset,0,1) - - I'm close I think...but a little stumped.

Sub FindCutWithInputBox()
With Application: .ScreenUpdating = False
    isearch = InputBox(Prompt:="Enter text or value to find", Title:="Search dialog")
        With .Offset(0, 1).Cut 'Got it wrong here?!?!?!
        End With
    [b2:i100].Replace What:=isearch, Replacement:=isearch, Lookat:=xlPart, SearchFormat:=False, ReplaceFormat:=True
.ScreenUpdating = True:
End With:
End Sub
Thanks and have a great day!

Hi, im new to this forum and i cant find the thread that helped me out with my issue.
I wanted to SUMIF a big block of numbers and I read here in a thread that you cant really use that formula because in SUMIF, the Range size has to be the same as the Sum Range, that is why the formula kept giving me just the Sum of the first column in my block of numbers (because the range = 1 column). So looking through these threads, there was someone with the same issue and Daddy Long Legs (i think) answered the question suggesting SUMPRODUCT with a criteria, for example:


I tried it and it worked, i checked it several time and did the calculations manually to make sure

which now makes me wonder, WHY? i have been trying to read about SUMPRODUCT and it says everywhere that it adds the products of numbers etc etc but I do not really understand when to use it or what is the difference
I mean, it works when your range size is different from your add range and you have a criteria and you cant use sumif, but

can someone explain?
sorry but i hate learning something new without actually understanding it because i want to make sure i put this new info to good use
thank you

Hi everyone!
I am covering a problem with using SUMPRODUCT with INDIRECT formulas
together. I have a list of students in a worksheet where i need to
count their GPA using these two formulas.

thank you in advance,


anara's Profile:
View this thread:

Is there an excel formula that can give me the offset value of each ocurrance of repeated data in a range of cell? I know there might be something in VBA, but I'm not quite familiar with it, and the spreadsheet will be passed on to others who are not VBA versed and would not know how to troubleshoot if the code blew up somehow.

I was told that i might be able to use Sumproduct with Offset, but not sure how to go about it. I've attached a sample file that explains how I would like the output to be and where. Any help would be appreciated. Thanks

No luck finding an answer? You could always try Google.