SumIf in Visible Cell Range

T

Terri

How do you set the range in the SumIf if you only want to refer to visible
cells in the range and not all the cells?

Thanks to all of You!
 
A

Alok

Hi,
I do not think there is a way to total only the visible rows if the rows are
hidden by the user. However, if the hiding takes place as a result of a Data
Filter then you can use the formula

=SUBTOTAL(9,D2:D7)

This will total all the visible rows as long as the hiding of the rows takes
place as a result of the filter.

Alok
 
A

Aladin Akyurek

Hi,
I do not think there is a way to total only the visible rows if the rows are
hidden by the user. However, if the hiding takes place as a result of a Data
Filter then you can use the formula

=SUBTOTAL(9,D2:D7)

This will total all the visible rows as long as the hiding of the rows takes
place as a result of the filter.

Alok

:
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
A

Alok

Thanks.

Alok

Aladin Akyurek said:
Hi,
I do not think there is a way to total only the visible rows if the rows are
hidden by the user. However, if the hiding takes place as a result of a Data
Filter then you can use the formula

=SUBTOTAL(9,D2:D7)

This will total all the visible rows as long as the hiding of the rows takes
place as a result of the filter.

Alok

:
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Click to expand...
 
R

Roger Govier

Hi Aladin

Just a small caveat, when rows are hidden by the user, then to have them
ignored by Subtotal(), you have to add 100 to the normal number for the
function required.
=SUBTOTAL(109,D2:D7)

=SUBTOTAL(9,D7:D27) will behave the same as in earlier versions of Excel
i.e. it will not ignore user hidden rows.

Regards

Roger Govier
 
T

topola

In Excel 2003 you can summarize hidden rows by using another set of
keys for subtotal (X+100) - see Excel Help for Subtotal function.

SUBTOTAL(9,D2:D7) = Summarize all (visible and invisible) rows
SUBTOTAL(109,D2:D7) = Summarize visible rows only

With the columns however this is not so simple. To know more see the
topic:
"Subtotal function for hidden row"
topola - 13 Paz. 16:04 microsoft.public.excel.worksheet.functions
Topola
 
Top