If column = value sum another column and place result in cell
 

I've edited the following code based on some of the ideas that I've
seen on this group. Ultimately I would like to evaluate two colums
for specific values and if those values are true then sum two other
columns. So basically I have two totals as a result. For testing
how
to do this I'm working with one column for eval and one column for
total as shown below, however it's providing me a #NAME? in cel J1
'We use the ActiveSheet but you can replace this with
With ActiveSheet
'define varibles for data
Dim ncgwsuactuals As Long
Dim ncgwsuallocated As Long
ncgwsuactuals = 0
ncgwsuallocated = 0
'We select the sheet so we can change the window view
.Select
'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row + 1
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
If .Cells(Lrow, "D").Value = "146268" Or _
.Cells(Lrow, "D").Value = "146269" Or _
.Cells(Lrow, "D").Value = "146270" Or _
.Cells(Lrow, "D").Value = "133957" Then .Cells(Lrow,
"G").Value = ncgwsuactuals + ncgwsuactuals
Next Lrow
With Range("J1")
.FormulaArray = "=ncgwsuactuals"
.Calculate
.Value = .Value
End With
End With

If column = value sum another column and place result in cell
 

A better expanation of what you are wanting to accomplish is in order. Your
code just does not make sense in a bunch of different respects. The things
you are doing to incease speed will have no effect on speed. Every time you
find a match you add ncgwsuactuals to itself, but it is 0 so you are adding 0
+ 0. At the end you try to add ncgwsuactuals as a named range to cell j1 even
though it is only a variable in your procedure (hence the #name error).

Are you looking for a value in column D and if you find it then you want to
accumulate the values from some other cells??? Finally display that total in
J1. If so why not use a formula in J1 (chain together 4 sumif's and you're
done).
--
HTH...

Jim Thomlinson


"S Himmelrich" wrote:

I've edited the following code based on some of the ideas that I've
seen on this group. Ultimately I would like to evaluate two colums
for specific values and if those values are true then sum two other
columns. So basically I have two totals as a result. For testing
how
to do this I'm working with one column for eval and one column for
total as shown below, however it's providing me a #NAME? in cel J1
'We use the ActiveSheet but you can replace this with
With ActiveSheet
'define varibles for data
Dim ncgwsuactuals As Long
Dim ncgwsuallocated As Long
ncgwsuactuals = 0
ncgwsuallocated = 0
'We select the sheet so we can change the window view
.Select
'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row + 1
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
If .Cells(Lrow, "D").Value = "146268" Or _
.Cells(Lrow, "D").Value = "146269" Or _
.Cells(Lrow, "D").Value = "146270" Or _
.Cells(Lrow, "D").Value = "133957" Then .Cells(Lrow,
"G").Value = ncgwsuactuals + ncgwsuactuals
Next Lrow
With Range("J1")
.FormulaArray = "=ncgwsuactuals"
.Calculate
.Value = .Value
End With
End With

If column = value sum another column and place result in cell
 

After reviewing the need, I've found the sumproduct code, which is a
simpler solution, but I can quite get the result I'm looking for - can
you help?

I think this would be a simpler solution:

=SUMPRODUCT(--('SOURCE DATA'!D2:D4000="133957")+('SOURCE DATA'!
D2:D4000="146268"),'SOURCE DATA'!G2:G4000)

however I'm not getting my total of column G based on the two values
sepecified in range D2:D4000

If column = value sum another column and place result in cell
 

Remove the unary (--) and also the quotes from your numbers.

HTH,
JP

On Feb 12, 1:02*pm, S Himmelrich wrote:
After reviewing the need, I've found the sumproduct code, which is a
simpler solution, but I can quite get the result I'm looking for - can
you help?

I think this would be a simpler solution:

=SUMPRODUCT(--('SOURCE DATA'!D2:D4000="133957")+('SOURCE DATA'!
D2:D4000="146268"),'SOURCE DATA'!G2:G4000)

however I'm not getting my total of column G based on the two values
sepecified in range D2:D4000

If column = value sum another column and place result in cell
 

thank you for the suggestion, I've done as you mentioned:

=SUMPRODUCT(('SOURCE DATA'!D2:D4000=133957)+('SOURCE DATA'!
D2:D4000=146268),'SOURCE DATA'!G2:G4000)

however the result is zero, there are values 0 in the G2:G4000 range
to add

If column = value sum another column and place result in cell
 

on a simpler scale I've event tried:

=SUMIF('SOURCE DATA'!D2:D4000,"=133957",'SOURCE DATA'!G2:G4000)

and don't get a total

If column = value sum another column and place result in cell
 

Are you looking for numbers or text strings??? If you are looking for numbers
then...

=SUMPRODUCT(--('SOURCE DATA'!D2:D4000=133957), 'SOURCE DATA'!G2:G4000)+
SUMPRODUCT(--('SOURCE DATA'!D2:D4000=1462268), (--('SOURCE
DATA'!D2:D4000<=1462270), 'SOURCE DATA'!G2:G4000)

Don't worry about the or stuff. Just use 2 sumproducts...

--
HTH...

Jim Thomlinson


"S Himmelrich" wrote:

thank you for the suggestion, I've done as you mentioned:

=SUMPRODUCT(('SOURCE DATA'!D2:D4000=133957)+('SOURCE DATA'!
D2:D4000=146268),'SOURCE DATA'!G2:G4000)

however the result is zero, there are values 0 in the G2:G4000 range
to add