SumIf Calculation in Code Problem

[Arturo]

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 I’m 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

 

[Joel]

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]

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.
Let’s 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]

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]

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