Forum Discussion
Count only shown values
Hi Estacion Area Poblacion Helper A 1 6 1 A 2 10 1 A 3 15 1 B 1 6 2 B 2 10 2 B 3 15 2 C 1 9 1 C 2 5 1 C 3 15 3 This is a dummy o...
The trick is explained here https://exceljet.net/formula/count-visible-rows-only-with-criteria
For such data
formula in F3 is
=SUMPRODUCT(($C$3:$C3=C3)*($D$3:$D3=D3)*SUBTOTAL(103,OFFSET($E$3,ROW($E$3:$E3)-ROW($E$3),0)))and drag it down.
Result is as
SergeiBaklan this works like a charm, but I gotta ask Why are we using the helper column?
SUBTOTAL(103,OFFSET($E$3,ROW($E$3:$E3)-ROW($E$3),0))I change it to column B and C - Area and Population and it works just fine
Thank you so much for your help!
marloz1024 , you are welcome.
SUMPRODUCT with such SUBTOTAL imitates SUMIFS() or COUNTIFS() are calculated on visible cells only since SUBTOTAL returns zero for hidden cells and 1 for visible ones.
If you don't need criteria you may use simple SUBTOTAL() to count/sum values.