Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1/COUNTIF(A2:A21;A2:A21))).

2) If a specific value or content inside 'Data' occurs n times it contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1/COUNTIF(A2:A21;A2:A21)))=SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1/COUNTIF(A2:A21;A2:A21)))=SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1/COUNTIF(A2:A21;A2:A21)))=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).

2) If a specific value or content inside 'Data' occurs n times it contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).

2) 2)Under array-evaluation the divisor COUNTIF(A2:A21;A2:A21) is evaluated for each element of A1:A21 in the second place for the complete range. If a specific value or content inside 'Data' occurs n times it therefore contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).

2)Under array-evaluation the divisor COUNTIF(A2:A21;A2:A21) is evaluated for each element of A1:A21 in the second place for counting over the complete range. If a specific value or content inside 'Data' occurs n times it therefore contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).

2)Under array-evaluation the divisor COUNTIF(A2:A21;A2:A21) is evaluated for each element of A1:A21 in the second place counting over the complete range. range given in the first place. If a specific value or content inside 'Data' occurs n times it therefore contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).

2)Under array-evaluation the divisor COUNTIF(A2:A21;A2:A21) is evaluated for each element of A1:A21 in the second place counting over the complete range given in the first place. If a specific value or content inside 'Data' occurs n times it therefore contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty"."empty".

If you want to study alternatives you may start with the formula in cell P3 of the first sheet of the attached demo. There are also some explanations. (As of 2014-07-14 22:31)

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example.

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).

2)Under array-evaluation the divisor COUNTIF(A2:A21;A2:A21) is evaluated for each element of A1:A21 in the second place counting over the complete range given in the first place. If a specific value or content inside 'Data' occurs n times it therefore contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

If you want to study alternatives you may start with the formula in cell P3 of the first sheet of the attached demo. There are also some explanations. (As of 2014-07-14 22:31)22:31)

(Editing:)
Just found the time to write a piece of BASIC code probably useful in the context.
Will I use it myself? Donno yet.

'=======================================================================================
'Negative test for row attribute IsVisible. 
'The SheetNumber ("1 based") MUST be passed via the second ParameterPlace.
'The function can work with a scalar argument and with an array of row numbers as well.
'PlausibilityChecks are not included. No ErrorHandling!
'=======================================================================================
Function rowIsHidden(pRow, pZ As Long, Optional pTrigger)
If NOT IsArray(pRow) Then
        Dim hRow(1 To 1, 1 To 1) As Long
    hRow(1, 1) = pRow
    pRow = hRow
EndIf
    Dim theSheet As Object 
    Dim theRR As Object  REM That's theRowRepresentant
    Dim rHidden(1 To Ubound(pRow, 1), 1 To 1), j As Long
theSheet = ThisComponent.Sheets(pZ-1)
For j = 1 To Ubound(pRow, 1)
    theRR = theSheet.GetCellByPosition(0, pRow(j, 1) - 1)
    rHidden(j, 1) = NOT theRR.Rows.IsVisible
Next j  

rowIsHidden = rHidden
End Function

A) Since SUMPRODUCT forces its parameters under array-evaluation anyway, there should be no difference if you enter the formula in standard mode. You need Ctrl+Shift+Enter only if you use SUM instead of SUMPRODUCT.
B) A slightly more explicit variant is =SUMPRODUCT(IF(A2:A21="";0;1)/COUNTIF(A2:A21;A2:A21))) where I replaced the placeholder 'Data' by a simple 1D-range as an example. (Editing 2017-05-02) The formula given above contained a wrongly placed closing parenthese. Rectified:
=SUMPRODUCT(IF(A2:A21="";0;1/COUNTIF(A2:A21;A2:A21)))
(End edit)

1) I do not clearly understand the term "static" here. What are the alternatives? Filtering a range is executed by hiding the rows not complying with the condition. Formulae always evaluate also hidden cells contained in their referenced ranges. This is essential for many evaluations. I confidently hope there is no option to change this behaviour. The 'Copy' tool ignores cells if hidden by applying a filter. Same with formatting, deleting, moving. How to get results restricting the evaluation of ranges to the visible part depends on the kind of evaluation. In your specific case you may have a filter condition which already was evaluated to a logical (Boolean) result in a helper column, say C per row. In this case the visible rows should show a TRUE there while the hidden rows have FALSE. If so, you can use =SUMPRODUCT(IF((A2:A21="")*(C2:C21=0);0;1)/COUNTIF(A2:A21;A2:A21))). Not shying at complicated formulae you may also evaluate the condition by a subexpression in the parameter place. There is no standard function testing rows for being hidden. If you coded a user function ISHIDDEN ROWISHIDDEN for the purpose based on row numbers yourself, you have the option
=SUMPRODUCT(IF((A2:A21="")*ISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21)))=SUMPRODUCT(IF((A2:A21="")*ROWISHIDDEN(ROW(A2:A21);0;1)/COUNTIF(A2:A21;A2:A21))).. (Editing 2017-05-02) The formula given above did not correctly combine the conditions. Rectified:
=SUMPRODUCT(IF(((A2:A21="")+ROWISHIDDEN(ROW(A2:A21);1;NOW()))>0;0;1/COUNTIF(A2:A21;A2:A21)))
The funny way the logical OR is transcribed here is a workaround needed due to the fact that the OR() function cannot return an array.
(End edit)

2)Under array-evaluation the divisor COUNTIF(A2:A21;A2:A21) is evaluated for each element of A1:A21 in the second place counting over the complete range given in the first place. If a specific value or content inside 'Data' occurs n times it therefore contributes 1/n n times to the result of SUMPRODUCT 1/n * n = 1. You see: You get a mandatorily integer result (=1) by adding nonintegers. That's the spell. Your original version of the formula is specifically veiling this by its way to exclude "empty".

If you want to study alternatives you may start with the formula in cell P3 of the first sheet of the attached demo. There are also some explanations. (As of 2014-07-14 22:31)

(Editing:)
Just found the time to write a piece of BASIC code probably useful in the context.
Will I use it myself? Donno yet.

'=======================================================================================
'Negative test for row attribute IsVisible. 
'The SheetNumber ("1 based") MUST be passed via the second ParameterPlace.
'The function can work with a scalar argument and with an array of row numbers as well.
'PlausibilityChecks are not included. No ErrorHandling!
'The result will be TRUE for filtered rows and for explicitly hidden rows as well.
'=======================================================================================
Function rowIsHidden(pRow, pZ As Long, Optional pTrigger)
If NOT IsArray(pRow) Then
        Dim hRow(1 To 1, 1 To 1) As Long
    hRow(1, 1) = pRow
    pRow = hRow
EndIf
    Dim theSheet As Object 
    Dim theRR As Object  REM That's theRowRepresentant
    Dim rHidden(1 To Ubound(pRow, 1), 1 To 1), j As Long
theSheet = ThisComponent.Sheets(pZ-1)
For j = 1 To Ubound(pRow, 1)
    theRR = theSheet.GetCellByPosition(0, pRow(j, 1) - 1)
    rHidden(j, 1) = NOT theRR.Rows.IsVisible
Next j  

rowIsHidden = rHidden
End Function