thanks however im still unsure as to how to use this function:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)
the 3 is the counting part but im not sure about what the rest means and what its used for....
with the formula listed in my first post i can change:
A2:A100="x"
to
Sheet3!AG:AG=A13
but other then that im pretty lost as to what to do to change this to fit my needs...
Edited for your scenario it becomesOriginally Posted by penfold1992
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!AG1,ROW(Sheet3!AG:AG)-ROW(Sheet3!AG1),0)),(Sheet3!AG:AG=A13)+0)
although using the whole column with SUMPRODUCT can be slow so you might want to use a more restricted range. In that formula the OFFSET part splits the column into individual cells so that SUBTOTAL can determine whether they are hidden or not. In the link from DILIP there's another link with a fuller explanation......
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks