Sum Cells based on attributes

[Guest]

Hello,
I have an excel spreadsheet that has sub-totals which are SUM;s on various
bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula
which would, for a particular column, add the values of the bolded rows

I can find the bolded row using by using:
If Selection.Font.Bold = True Then......

and I can insert a statement for one row by using something like:
cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)"

but I can't seem to find a way to concatinate all the cell references
together and place them in the last row. Since they use relative
referencing, it varies by the time I place it in the final cell.

Thanks for your help.

 

[JE McGimpsey]

Take a look here:

http://mcgimpsey.com/excel/udfs/sumbold.html

 

[Guest]

Thanks for answering. But I would like to put a SUM formula in a particular
cell, not just a sum of all the values. The formula I'm looking to insert
would look something like (=K68+K74+K84). The column is constant, but only
the rows are variable.
Do you have a way to make the formula?
--
Programmer on Budget

Take a look here:

http://mcgimpsey.com/excel/udfs/sumbold.html

Hello,
I have an excel spreadsheet that has sub-totals which are SUM;s on various
bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula
which would, for a particular column, add the values of the bolded rows

I can find the bolded row using by using:
If Selection.Font.Bold = True Then......

and I can insert a statement for one row by using something like:
cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)"

but I can't seem to find a way to concatinate all the cell references
together and place them in the last row. Since they use relative
referencing, it varies by the time I place it in the final cell.

Thanks for your help.

 

[JE McGimpsey]

How are the rows variable? If only in that the cells are bold, then,
using my UDF:

=SumBold(K1:K100)

If they vary in other ways, I'm not sure what you're trying to
accomplish.

Thanks for answering. But I would like to put a SUM formula in a particular
cell, not just a sum of all the values. The formula I'm looking to insert
would look something like (=K68+K74+K84). The column is constant, but only
the rows are variable.
Do you have a way to make the formula?
--
Programmer on Budget

Take a look here:

http://mcgimpsey.com/excel/udfs/sumbold.html

Hello,
I have an excel spreadsheet that has sub-totals which are SUM;s on
various
bolded rows. Now, at the bottom of the spredsheet I'd like insert a
formula
which would, for a particular column, add the values of the bolded rows

I can find the bolded row using by using:
If Selection.Font.Bold = True Then......

and I can insert a statement for one row by using something like:
cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)"

but I can't seem to find a way to concatinate all the cell references
together and place them in the last row. Since they use relative
referencing, it varies by the time I place it in the final cell.

Thanks for your help.

 

[Tom Ogilvy]

for i = 1 to intRowVar - 1
if cells(i,intColVar).Font.Bold then
if rng is nothing then
set rng = cells(i,intColVar)
else
set rng = union(rng,cells(i,intColVar)
end if
endif
Next
if not rng is nothing then
cells(intRowVar, intColVar).Formula = "=Sum( & _
rng.Address(1,1,xlA1,False) & ")"
End if


I assume intRowVar and intColVar have values since you used them in your
example. Change the 1 in 1 to intRowVar-1 to be the row of the first cell
you want to check.
--
Regards,
Tom Ogilvy

Thanks for answering. But I would like to put a SUM formula in a particular
cell, not just a sum of all the values. The formula I'm looking to insert
would look something like (=K68+K74+K84). The column is constant, but only
the rows are variable.
Do you have a way to make the formula?
--
Programmer on Budget

Take a look here:

http://mcgimpsey.com/excel/udfs/sumbold.html

Hello,
I have an excel spreadsheet that has sub-totals which are SUM;s on various
bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula
which would, for a particular column, add the values of the bolded rows

I can find the bolded row using by using:
If Selection.Font.Bold = True Then......

and I can insert a statement for one row by using something like:
cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)"

but I can't seem to find a way to concatinate all the cell references
together and place them in the last row. Since they use relative
referencing, it varies by the time I place it in the final cell.

Thanks for your help.

 

[Guest]

Tom,
That did it. That's exactly what I needed. Many Thanks.
BTW, you were missing a double-quotes after:
"=SUM(
The working code looks like:
"=SUM("
It was very easy to figure out. Thanks again for all your help. Awesome!

--
Programmer on Budget

for i = 1 to intRowVar - 1
if cells(i,intColVar).Font.Bold then
if rng is nothing then
set rng = cells(i,intColVar)
else
set rng = union(rng,cells(i,intColVar)
end if
endif
Next
if not rng is nothing then
cells(intRowVar, intColVar).Formula = "=Sum( & _
rng.Address(1,1,xlA1,False) & ")"
End if


I assume intRowVar and intColVar have values since you used them in your
example. Change the 1 in 1 to intRowVar-1 to be the row of the first cell
you want to check.
--
Regards,
Tom Ogilvy

Thanks for answering. But I would like to put a SUM formula in a particular
cell, not just a sum of all the values. The formula I'm looking to insert
would look something like (=K68+K74+K84). The column is constant, but only
the rows are variable.
Do you have a way to make the formula?
--
Programmer on Budget

Take a look here:

http://mcgimpsey.com/excel/udfs/sumbold.html

Hello,
I have an excel spreadsheet that has sub-totals which are SUM;s on various
bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula
which would, for a particular column, add the values of the bolded rows

I can find the bolded row using by using:
If Selection.Font.Bold = True Then......

and I can insert a statement for one row by using something like:
cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)"

but I can't seem to find a way to concatinate all the cell references
together and place them in the last row. Since they use relative
referencing, it varies by the time I place it in the final cell.

Thanks for your help.