Sumproduct igoring hidden value

[Edward Wang]

Hi,
How can I use sumproduct function and igore hidden value. The function usage
likes below:
=sumproduct((range1=criteria1)*(rang2=criterial2)*rang4), and I am using
filter on range3.
I expect the function result can igore the any hidden value I made on range 3.

P.S. each range is one column with same row number.

Thanks,

 

[Jacob Skaria]

With data from row1 to row10 try the below...sum col C based on two
conditions in A and B

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1:C10,ROW(C1:C10)-MIN(
ROW(C1:C10)),0,1)),--(A1:A10="a")*(B1:B10="b"),C1:C10)


If this post helps click Yes

 

[Gary''s Student]

First enter the following User Defined FUnction in a standard module:

Public Function visi(rr As Range) As Varian
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' rr must be a column or piece of a colum
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.Volatile
Dim r As Range
Dim v()
ReDim v(1 To rr.Count)
i = 1
For Each r In rr
If r.EntireRow.Hidden = False Then
v(i) = 1
Else
v(i) = 0
End If
i = i + 1
Next
visi = Application.Transpose(v)
End Function

The function will return 0 if the row is hidden, otherwise 1

Then something like:

=SUMPRODUCT((A1:A100=1)*(B1:B100="pass")*(visi(C1:C100)))

can be used. This allows SUMPRODUCT to be used on an AutoFiltered table.

 

[Edward Wang]

Yes it really works. I used "Subtotal" twice to cover two columns with auto
filters.
Thanks!