Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SumIf in Visible Cell Range
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! |
#2
|
|||
|
|||
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 "Terri" wrote: 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! |
#3
|
|||
|
|||
On Excel 2003, you can also subtotal "the visible rows if the rows are
hidden by the user". Alok wrote: 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 "Terri" wrote: 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! -- [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. |
#4
|
|||
|
|||
Thanks.
Alok "Aladin Akyurek" wrote: On Excel 2003, you can also subtotal "the visible rows if the rows are hidden by the user". Alok wrote: 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 "Terri" wrote: 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! -- [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. |
#5
|
|||
|
|||
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 Aladin Akyurek wrote: On Excel 2003, you can also subtotal "the visible rows if the rows are hidden by the user". Alok wrote: 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 "Terri" wrote: 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! |
#6
|
|||
|
|||
SumIf in Visible Cell Range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell linked to a range of cell values in different sheet | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Reveal cell formats and extendable range in tool/statusbar/icon. | Excel Worksheet Functions |