LinkBack URL
About LinkBacks
Hi
I currently use the SUMPRODUCT formula below and wondered how I can include the SUBTOTAL function so that when I filter the data only the visible cells are returned.
=SUMPRODUCT(--(A$34:A$2472=A3),--(AA$34:AA$2472=1),--(U$34:U$2472))
Many thanks
Hi
I currently use the SUMPRODUCT formula below and wondered how I can include the SUBTOTAL function so that when I filter the data only the visible cells are returned.
=SUMPRODUCT(--(A$34:A$2472=A3),--(AA$34:AA$2472=1),--(U$34:U$2472))
Many thanks
Hello Chilli,
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A$34,ROW(A$34:A$2472)-ROW(A$34),)),--(A$34:A$2472=A3),--(AA$34:AA$2472=1),U$34:U$2472)
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
many many thanks.
Sorry one more if you don't mind
How do I include it the formula below
=COUNTIF(A34:A2472,A3)
=SUMPRODUCT(--(A$34:A$2472=A3),--(AA$34:AA$2472=1),--(U$34:U$2472),--SUBTOTAL(3,OFFSET(A$34,ROW(A$34:A$2472)-MIN(ROW(A$34:A$2472)),)))
Try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Countif(a3,a34:a2472)
can include like this
Originally Posted by Haseeb A
Isn't in the formula? --(A$34:A$2472=A3)
Not sure I understand
=COUNTIF(A34:A2472,A3)
This is a separate formula that I use to count the row identifiers I have in column A to match the identifier in Cell A3. Just wanted to do the same and count these only if they remain after filtering.
Thanks
OK Chilli. My bad.
The answer is already in your original formula, just add SUBTOTAL in the front & remove last two criteria.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A$34,ROW(A$34:A$2472)-ROW(A$34),)),--(A$34:A$2472=A3))
Ah yes, I see. Thanks for that, works a treat
This is the final word we all expect. Thank you Chilli for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks