Count visible&non visible rows on filter

Closed
Report
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
-
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
-
Hello,
I want to count all the visible and non visible rows even when data is filtered.
Currently using SUMPRODUCT(($E$2:$E$15="Incident")*SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$15)-ROW($B$2),))) but its only looking at filtered data, the SUBTOTAL aparantly look only at filtered data. How do i change it to look at both filtered and not filtered data.




1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Simple count and sumif should give you all count, I would think. And you can use the difference to find out what rows are hidden since you already have count and sum for rows that are visible
1
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012

TX, i decided that it must have 2 conditions and used
=SUM(IF(C2:C200="Car")*(E2:E200="Incident"),M2:M200)) and CTRL+ALT+ENTER
0