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

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

-matt

## Related Results

### Sumproduct with Offsets

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?

=SUMPRODUCT((OFFSET('Source'!\$E\$37,0,YEAR(F\$7)-YEAR(\$E\$7),COUNTA('Source'!C38:C41),0)),(OFFSET('Source'!\$M\$37,0,YEAR(F\$7)-YEAR(\$E\$7),COUNTA('Source'!C38:C41),0)))

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?

Thks

### Sumproduct with offset

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

```
VB:
""))

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.

### Sumproduct with day criteria

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,

Biz

### SUMPRODUCT with formula created blank cell = #VALUE!

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?

### Sumproduct with offset

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

### VBA -- SUMPRODUCT with VARIABLES and ORing

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:

---A-------B------C------D---
--PGV----2008---Real---400--
--OAK----2008---Real---200--
--BBB-----2008---Fake--100--
--PGV-----2007---Real---600--

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

PROBLEM VBA:

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

HINT #1 TO SOLUTION:

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

HINT #2 TO SOLUTION:

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

IN SUMMARY:

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

### SUMPRODUCT with OR operator

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!
Thanks!

### Sumproduct with Condition OR Sumproduct with ADDRESS function - HE

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!

Thanks!

### Sumproduct with different ranges

Howdy

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

Thanks

KJ

### Creating a macro with a macro with offsets

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.

Thanks

### Using MAX with OFFSET and MATCH

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):
A B
T600 11
T600 6
T600 15
T601 2
T601 12
The formulas I have tried are:
=MAX(OFFSET(\$A\$1,MATCH(\$A1,\$A\$1:\$A\$5,0)-1,1,-1,-1))
=LARGE(INDEX(OFFSET(\$L\$2,MATCH(\$L2,\$L\$2:\$L\$6,0)-1,1,-1,-1),1),1)
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
Joe

### Sumproduct with Arrays of Different Sizes

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

### LOOKUP with OFFSET?

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.

Thanks

Robert

### Define Name with Offset

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

```

### Vlookup with offset

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?

### Help with offset

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, _
MatchCase:=False).Row
On Error GoTo 0

End Function

### Problem With OFFSET

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.

Thoughts?

Thanks

### Sumproduct with wildcard

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

=SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))

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

--
Mark

### Problem with OFFSET function used in Named Ranges

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

```

### Using sumproduct with containing a word

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.

### Using Find then cut with offset in VBA

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!

### Sumproduct with criteria

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:

=SUMPRODUCT((A1:A10="TRUE")*B1:D10)

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

### Need help with using SUMPRODUCT with INDIRECT

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.

Anara.

--
anara
------------------------------------------------------------------------
anara's Profile: http://www.hightechtalks.com/m782