SUM with variable range

[Jose]

Hi,

I don't know if this can be done, given my limited experience with VB
and Excel. Let me try to simplify the problem as much as I can,
maintaining the issue as I have it.

Imagine you have this data. In column A, the different products you
sell. Column B, with the price. Column C, with the units sold. Those
three columns get downloaded from a database. Now, I want to populate
column D as follows:

A B
C D
1 " Model A" $200 10
=B1*C1
2 " Model B" $100 15
=B2*C2
3 " Model C" $500 25
=B3*C3
4 "Family 1" 30
=SUM(D13)
5 " Model D" $100 15
=B5*C5
6 " Model E" $200 10
=B6*C6
7 "Family 2" 25
=SUM(D56)

I can easily populate column D, EXCEPT the "SUM" function. I do a "For
each" loop, selecting all data in column A, and if the first character
in cell A1, A2, etc, is "space", I can input the multiplication,
although not too elegantly... I have a cell, hidden in a safe place,
with the formula that multiplies the 2 adjacent cells. So I can just
copy that cell into D1, D2, D3, D5 and D6.

The problem is that, when I detect that in A4, the first character is
not space, I don't know how to enter the SUM formula. NOTE: Sometimes,
the "Family 1" will have 3 submodels, as above, but it can change and
be 5, or 2 models. So I don't know, in advance, how many cells I will
have to add.

I was thinking about having a counter that tells me from which row to
which row I need to calculate the sum, but I don't know what to do
with this two values! In this example, in D4 I can have the values 1
and 3, as the extremes, and in cell D7 I can have the values 5 and 6
as extremes, but I don't know what to do with them!!

Ideas?

Thanks!!
Jose

 

[Jose]

Another try with the table...

A B C D
1 " Model A" $200 10 =B1*C1
2 " Model B" $100 15 =B2*C2
3 " Model C" $500 25 =B3*C3
4 "Family 1" - 30 =SUM(D13)
5 " Model D" $100 15 =B5*C5
6 " Model E" $200 10 =B6*C6
7 "Family 2" - 25 =SUM(D56)

Sorry!
Jose

 

[NickHK]

Jose,
Maybe add a column for "Family", then look at data.SubTotals.

NickHK

 

[NickHK]

That should be :
Maybe add a column for "Family", then look at Data>SubTotals menu.

NickHK

 

[Jose]

I am not familiar with the Data>SubTotals menu, and don't know how it
works...

However, I came up with a solution that works. I basically create a
string with the formula I want to insert, like this:

(selection is column A)

a = 0
Str1 = "=SUM(R[-"
For Each b In selection.Cells
If Left(b.Value, 1) = " " Then
... here I copy the easy "=B1*C1" formula
Else
Str1 = Str1 + Trim(Str(a)) + "]C:R[-1]C)"
b.Offset(0, 3).FormulaR1C1 = Str1
a = -1
Str1 = "=SUM(R[-"
End If
a = a + 1
Next

Every time I put the sum formula for the cells above, I restart the
Str1 and a variables. It works beautifully!

Thanks anyway!
Jose

 

[NickHK]

Jose,
If you tried it you would see how it works.
But if you already have a solution, well go with that.

NickHK

I am not familiar with the Data>SubTotals menu, and don't know how it
works...

However, I came up with a solution that works. I basically create a
string with the formula I want to insert, like this:

(selection is column A)

a = 0
Str1 = "=SUM(R[-"
For Each b In selection.Cells
If Left(b.Value, 1) = " " Then
... here I copy the easy "=B1*C1" formula
Else
Str1 = Str1 + Trim(Str(a)) + "]C:R[-1]C)"
b.Offset(0, 3).FormulaR1C1 = Str1
a = -1
Str1 = "=SUM(R[-"
End If
a = a + 1
Next

Every time I put the sum formula for the cells above, I restart the
Str1 and a variables. It works beautifully!

Thanks anyway!
Jose

That should be :
Maybe add a column for "Family", then look at Data>SubTotals menu.

NickHK

 

[Jose]

I did try it, but couldn't really understand how it works. I tried
later again, and now I see how it works, and how it can be useful in
the future. But for this case, it messes up the table. The other
solution would be a better fit.

Thanks anyway, I learned another good function in Excel!

Jose

Jose,
If you tried it you would see how it works.
But if you already have a solution, well go with that.

NickHK

I am not familiar with the Data>SubTotals menu, and don't know how it
works...

However, I came up with a solution that works. I basically create a
string with the formula I want to insert, like this:

(selection is column A)

a = 0
Str1 = "=SUM(R[-"
For Each b In selection.Cells
If Left(b.Value, 1) = " " Then
... here I copy the easy "=B1*C1" formula
Else
Str1 = Str1 + Trim(Str(a)) + "]C:R[-1]C)"
b.Offset(0, 3).FormulaR1C1 = Str1
a = -1
Str1 = "=SUM(R[-"
End If
a = a + 1
Next

Every time I put the sum formula for the cells above, I restart the
Str1 and a variables. It works beautifully!

Thanks anyway!
Jose

- Show quoted text -