SumIf Calculation in Code Problem
 

I think this is what you need

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<" & XXX, ActiveWorkbook.Sheets("Sheet1").Columns(1))

The < is a string and XXX is a variable which is a number. The & combines
a strinhg and a number and makes the results a string.


"Arturo" wrote:

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

The above code works but, the problem Im encountering has to do with
factoring in a number associated with a variable, X, into the denominator of
this formula. X resides elsewhere, I can pull it with no problem. Whatever
SumIf of column 1 is, I want to add X to that summation but am not finding
the correct syntax to do so. Any direction would be appreciated.

Arturo

SumIf Calculation in Code Problem
 

Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

This sums numbers in column 1 when the string XXX does not reside in column
2.
Lets call the variable I need to add Var1 instead of X.

A1 = 10, A2 = XXX
B1 = 10, B2 = blank
Var1 = 10

The result in the divisor of the full formula should be 20.



"Joel" wrote:

I think this is what you need

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<" & XXX, ActiveWorkbook.Sheets("Sheet1").Columns(1))

The < is a string and XXX is a variable which is a number. The & combines
a strinhg and a number and makes the results a string.


"Arturo" wrote:

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

The above code works but, the problem Im encountering has to do with
factoring in a number associated with a variable, X, into the denominator of
this formula. X resides elsewhere, I can pull it with no problem. Whatever
SumIf of column 1 is, I want to add X to that summation but am not finding
the correct syntax to do so. Any direction would be appreciated.

Arturo

SumIf Calculation in Code Problem
 

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<" & Var1, ActiveWorkbook.Sheets("Sheet1").Columns(1))

the columns 1 & 2 can also be replaced with variables. Something like
"sheet1" can also be replaces with "sheet" & Mysheet
"Arturo" wrote:

Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

This sums numbers in column 1 when the string XXX does not reside in column
2.
Lets call the variable I need to add Var1 instead of X.

A1 = 10, A2 = XXX
B1 = 10, B2 = blank
Var1 = 10

The result in the divisor of the full formula should be 20.



"Joel" wrote:

I think this is what you need

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<" & XXX, ActiveWorkbook.Sheets("Sheet1").Columns(1))

The < is a string and XXX is a variable which is a number. The & combines
a strinhg and a number and makes the results a string.


"Arturo" wrote:

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

The above code works but, the problem Im encountering has to do with
factoring in a number associated with a variable, X, into the denominator of
this formula. X resides elsewhere, I can pull it with no problem. Whatever
SumIf of column 1 is, I want to add X to that summation but am not finding
the correct syntax to do so. Any direction would be appreciated.

Arturo

SumIf Calculation in Code Problem
 

"<XXX" has to be inplace as an exclusion flag.
"<" & Var1 still doesn't factor in the number associated with Var1 nor
does "<XXX" & Var1. Still cannot find a way to add Var1 into the divisor of:

Numerator/ _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

"Joel" wrote:

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<" & Var1, ActiveWorkbook.Sheets("Sheet1").Columns(1))

the columns 1 & 2 can also be replaced with variables. Something like
"sheet1" can also be replaces with "sheet" & Mysheet
"Arturo" wrote:

Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

This sums numbers in column 1 when the string XXX does not reside in column
2.
Lets call the variable I need to add Var1 instead of X.

A1 = 10, A2 = XXX
B1 = 10, B2 = blank
Var1 = 10

The result in the divisor of the full formula should be 20.



"Joel" wrote:

I think this is what you need

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<" & XXX, ActiveWorkbook.Sheets("Sheet1").Columns(1))

The < is a string and XXX is a variable which is a number. The & combines
a strinhg and a number and makes the results a string.


"Arturo" wrote:

ActiveWorkbook.Sheets("Sheet1").Cells(i, 3).Value = _
ActiveWorkbook.Sheets("Sheet1").Cells(i, 1) / _
Application.SumIf(ActiveWorkbook.Sheets("Sheet1"). Columns(2), _
"<XXX", ActiveWorkbook.Sheets("Sheet1").Columns(1))

The above code works but, the problem Im encountering has to do with
factoring in a number associated with a variable, X, into the denominator of
this formula. X resides elsewhere, I can pull it with no problem. Whatever
SumIf of column 1 is, I want to add X to that summation but am not finding
the correct syntax to do so. Any direction would be appreciated.

Arturo