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

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

- Sumproduct with Offsets
- Sumproduct with offset
- Sumproduct with day criteria
- SUMPRODUCT with formula created blank cell = #VALUE!
- Sumproduct with offset
- VBA -- SUMPRODUCT with VARIABLES and ORing
- SUMPRODUCT with OR operator
- Sumproduct with Condition OR Sumproduct with ADDRESS function - HE
- Sumproduct with different ranges
- Creating a macro with a macro with offsets
- Using MAX with OFFSET and MATCH
- Sumproduct with Arrays of Different Sizes
- LOOKUP with OFFSET?
- Define Name with Offset
- Vlookup with offset
- Help with offset
- Problem With OFFSET
- Sumproduct with wildcard
- Problem with OFFSET function used in Named Ranges
- Using sumproduct with containing a word
- Using Find then cut with offset in VBA
- Sumproduct with criteria
- Need help with using SUMPRODUCT with INDIRECT
- Display Corresponding Data For Certain Values Of 1 Column

=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

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

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.

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

See attached.

Any suggestions on a work-around?

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

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!

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!

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

Thanks

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

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

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

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

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?

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

LastRowRange(ActiveSheet)).Address

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

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(--(F:F="NPT*"),--(A:A="PC*"))

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

--

Mark

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.

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.

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 SubThanks and have a great day!

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

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.

--

anara

------------------------------------------------------------------------

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

View this thread: http://www.hightechtalks.com/t2338082

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