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 of a table I´m working on:
- I have stations, A, B, C, each station serves an area and each area has X population.
- Because of their location, some stations share areas and population, station A and B share Area 1 and 2, and population
- And other station share areas but not total their population, station C share areas 1 and 2 but because of its location, it serves to a bigger population within those two areas.
I need that, when selecting any station, I can get a SUM of the population of UNIQUE VALUES being a unique value AREA + POPULATION. For that, I though of a helper column, that gives value 1 to the first case registre and 2, 3, 4... to following cases, this work fine for full table but it doesn´t when table is filterd.
| Estacion | Area | Poblacion | Helper |
| C | 1 | 9 | 1 |
| C | 2 | 5 | 1 |
| C | 3 | 15 | 1 |
This is and example of what I´m looking for, in previous table on the last row of Helper column, the value would be 3 as we had that case 3 times, now that table is filtered, I need for helper column to show 1.
3 Replies
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.
