sum (column 2 of namedrange)

  • Thread starter Thread starter Carl Brehm
  • Start date Start date
C

Carl Brehm

=(SUM(Onhand) totals all cells in the range onhand hidden or not
1. How do you sum just the unhidden
2. how to sum just column 2 of a range or 1 row of a range?

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies
 
Carl

1. =SUBTOTAL(9, Onhand)
Be aware, that the formula only works, when the rows
have been hidden by *filtering*

2.
First column:
=SUM(OFFSET(E1:F6,0;0,,1))
Second column:
=SUM(OFFSET(E1:F6,0;1,,1))
etc.

First row:
=SUM(OFFSET(E1:F6,0;0,1,))
Second row:
=SUM(OFFSET(E1:F6,1;0,1,))
etc.


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Leo said:
Carl
2.
First column:
=SUM(OFFSET(E1:F6,0;0,,1)) :::or =SUM(INDEX(E1:F6,0,1)
Second column:
=SUM(OFFSET(E1:F6,0;1,,1)) :::or =SUM(INDEX(E1:F6,0,2)
etc.

First row:
=SUM(OFFSET(E1:F6,0;0,1,)) :::or =SUM(INDEX(E1:F6,1,0)
Second row:
=SUM(OFFSET(E1:F6,1;0,1,)) :::or =SUM(INDEX(E1:F6,2,0)
etc.
Click to expand...

And the semicolon in Leo Heuser's formulas is a typo.

Alan Beban
 
Thanks, Alan!

To Carl:
And the missing righthand parenthesis in Alan's
formulae is a typo <g>

LeoH
 
Okay now why use index instead of offset. What are the advantages and
disadvantages of each way? Is there times when one is better than the other?


--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies
 
Back
Top