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,

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
---------------
Jacob Skaria


"Edward Wang" wrote:

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,

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

Public Function visi(rr As Range) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''
' rr must be a column or piece of a column
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''
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.
--
Gary''s Student - gsnu200908


"Edward Wang" wrote:

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,

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Edward Wang" wrote:

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,