Countif only visible cells

[Jonathan78]

Hi all,

Is it possible to Countif only visible cells?
Rows are continually hidden and made visible again, but the range is constant.

btw. I don't like to use a filter in this layout.

[arthurbr]

Chip has a UDF for visible cells at this page

[DonkeyOte]

see SUBTOTAL ... if you're not using AutoFilter then see the 101+ functions.

If you want to conduct conditional calcs then you need to either

a) embed the SUBTOTAL within an OFFSET within a SUMPRODUCT

b) use a Visible Flag in a column at source (eg a SUBTOTAL COUNTA per row which will return 1 (visible) or 0 (not visible))
then you might consider using a SUMIF if single condition eg SUMIF(criteriacolumn,criteria,subtotalcountacolumn)

[Jonathan78]

Chip has a UDF for visible cells at this page

Thanks for the link Arthur, bit it doesn't address the countif function

[Jonathan78]

see SUBTOTAL ... if you're not using AutoFilter then see the 101+ functions.

If you want to conduct conditional calcs then you need to either

a) embed the SUBTOTAL within an OFFSET within a SUMPRODUCT

b) use a Visible Flag in a column at source (eg a SUBTOTAL COUNTA per row which will return 1 (visible) or 0 (not visible))
then you might consider using a SUMIF if single condition eg SUMIF(criteriacolumn,criteria,subtotalcountacolumn)

I tried this,

=SUMPRODUCT(SUBTOTAL(3;OFFSET(G14:G31;ROW(G14:G31)-ROW(G14);0;1));--(G14:G31="N"))*K12

but it still counts the hidden cells too

[DonkeyOte]

it still counts the hidden cells too

Perhaps re-read my prior post ?

if you're not using AutoFilter then see the 101+ functions.

Note: the 101+ don't exist prior to XL2003

[Jonathan78]

Now I understand the 101+ functions.
Thanks for helping!