using SUM to add up only some numbers in a column

[Joe Palazzo]

I have a column of numbers and only wish to SUM certain random numbers to be
selected. I thought that I could select the numbers desired and use autosum
to generate a total. But I think that I am doing something wrong.

 

[Héctor Miguel]

hi, Joe !

I have a column of numbers and only wish to SUM certain random numbers to be selected.
I thought that I could select the numbers desired and use autosum to generate a total.
But I think that I am doing something wrong.

why not include in yor post some "minor" details ? (i.e.)
- what do you have (and where)
- what do you want (and where)
- what do you try (and how)

(if possible... +/- exactly)

regards,
hector.

 

[Bob Phillips]

Why would you ever want to add random numbers?

 

[Joe Palazzo]

Okay. In the column, I have a sales figure followed by a revenue figure
followed by a commission figure and then a blank cell; then, repeat sales,
revenue, commmission, blank cell. This series is repeated 12 times for
twelve months. At the bottom of the column I want the total sales in the
first cell followed by the total revenue followed by the total commission. I
know that I can create =sum(a1,a5,a9,a13, etc), but I thought that there
should be a way using the autosum icon after selecting the cells I wanted in
the total cell.

 

[Héctor Miguel]

hi, Joe !

Okay. In the column, I have a sales figure followed by a revenue figure followed by a commission figure and then a blank cell
then, repeat sales, revenue, commmission, blank cell. This series is repeated 12 times for twelve months.
At the bottom of the column I want the total sales in the first cell followed by the total revenue followed by the total commission.
I know that I can create =sum(a1,a5,a9,a13, etc), but I thought that there should be a way
using the autosum icon after selecting the cells I wanted in the total cell...

(i.e.) data in [A1:A23] every 4 rows (sales, revenue, commission, blank)
in [A25] you need the sum of sales (a1,a5,a9,a13)

=sumproduct(--(mod(row(a1:a23),4)=row(a1)),a1:a23)

copy/drag down [A26:A27] and you will get the sum of revenue (A26) and commission (A27)
(modify/adapt according your needs)

hth,
hector.

 

[Joe Palazzo]

Hector, you're a real excel pro and thanks for the formula. I would have had
to take a lot of time to figure that one out on my own. I'll use it, but
tell me, is there a sum or autosum capability that would allow me to select
every fourth cell in a column and then automatically total them up as you
would in a column of contigious numbers? It just seems to me to be something
that MS would do that would be a real neat capability rather than having to
know Excel so well as to come up with such a complicated formula to do
something that could be done so simply.

hi, Joe !

Okay. In the column, I have a sales figure followed by a revenue figure followed by a commission figure and then a blank cell
then, repeat sales, revenue, commmission, blank cell. This series is repeated 12 times for twelve months.
At the bottom of the column I want the total sales in the first cell followed by the total revenue followed by the total commission.
I know that I can create =sum(a1,a5,a9,a13, etc), but I thought that there should be a way
using the autosum icon after selecting the cells I wanted in the total cell...

(i.e.) data in [A1:A23] every 4 rows (sales, revenue, commission, blank)
in [A25] you need the sum of sales (a1,a5,a9,a13)

=sumproduct(--(mod(row(a1:a23),4)=row(a1)),a1:a23)

copy/drag down [A26:A27] and you will get the sum of revenue (A26) and commission (A27)
(modify/adapt according your needs)

hth,
hector.

 

[Héctor Miguel]

hi, Joe !

... is there a sum or autosum capability that would allow me to select every fourth cell in a column
and then automatically total them up as you would in a column of contigious numbers?
It just seems to me to be something that MS would do that would be a real neat capability
rather than having to know Excel so well as to come up with such a complicated formula
to do something that could be done so simply...

i don't know of any "native" function with such capabilities (auto-sum arbitrary selected cells/range)
but... (if excel doesn't have one, you can build at your own) you might want to give a try to the folowing:

- a sub procedure (which could be assiged to a shortcut ?) like:

Sub ArbitraryRangeSum()
On Error Resume Next
Application.InputBox( _
Prompt:="Select TargetCell", _
Title:="Sum formula in...", _
Default:=ActiveCell.Address, _
Type:=8).Cells(1).Formula = _
"=sum(" & Selection.Address & ")"
End Sub

select one, two or more (non)contiguous cells/range and run the macro above
if you don't need absolute references... change the last line:

from: -> "=sum(" & Selection.Address & ")"
to: -> "=sum(" & Selection.Address(0, 0) & ")"

hth,
hector.

__ OP __

Okay. In the column, I have a sales figure followed by a revenue figure followed by a commission figure and then a blank cell
then, repeat sales, revenue, commmission, blank cell. This series is repeated 12 times for twelve months.
At the bottom of the column I want the total sales in the first cell followed by the total revenue followed by the total commission.
I know that I can create =sum(a1,a5,a9,a13, etc), but I thought that there should be a way
using the autosum icon after selecting the cells I wanted in the total cell...

(i.e.) data in [A1:A23] every 4 rows (sales, revenue, commission, blank)
in [A25] you need the sum of sales (a1,a5,a9,a13)

=sumproduct(--(mod(row(a1:a23),4)=row(a1)),a1:a23)

copy/drag down [A26:A27] and you will get the sum of revenue (A26) and commission (A27)
(modify/adapt according your needs)

 

[Joe Palazzo]

Thanks, Hector. I'll try it.

You've helped me a great deal and I appreciate it very much.

Have a great day.

Joe

hi, Joe !

... is there a sum or autosum capability that would allow me to select every fourth cell in a column
and then automatically total them up as you would in a column of contigious numbers?
It just seems to me to be something that MS would do that would be a real neat capability
rather than having to know Excel so well as to come up with such a complicated formula
to do something that could be done so simply...

i don't know of any "native" function with such capabilities (auto-sum arbitrary selected cells/range)
but... (if excel doesn't have one, you can build at your own) you might want to give a try to the folowing:

- a sub procedure (which could be assiged to a shortcut ?) like:

Sub ArbitraryRangeSum()
On Error Resume Next
Application.InputBox( _
Prompt:="Select TargetCell", _
Title:="Sum formula in...", _
Default:=ActiveCell.Address, _
Type:=8).Cells(1).Formula = _
"=sum(" & Selection.Address & ")"
End Sub

select one, two or more (non)contiguous cells/range and run the macro above
if you don't need absolute references... change the last line:

from: -> "=sum(" & Selection.Address & ")"
to: -> "=sum(" & Selection.Address(0, 0) & ")"

hth,
hector.

__ OP __

Okay. In the column, I have a sales figure followed by a revenue figure followed by a commission figure and then a blank cell
then, repeat sales, revenue, commmission, blank cell. This series is repeated 12 times for twelve months.
At the bottom of the column I want the total sales in the first cell followed by the total revenue followed by the total commission.
I know that I can create =sum(a1,a5,a9,a13, etc), but I thought that there should be a way
using the autosum icon after selecting the cells I wanted in the total cell...

(i.e.) data in [A1:A23] every 4 rows (sales, revenue, commission, blank)
in [A25] you need the sum of sales (a1,a5,a9,a13)

=sumproduct(--(mod(row(a1:a23),4)=row(a1)),a1:a23)

copy/drag down [A26:A27] and you will get the sum of revenue (A26) and commission (A27)
(modify/adapt according your needs)