counting cells in filtered list

[raydaw]

I have a 3 column spreadsheet, the third column is conditionall
formatted and the spreadsheet has an automatic filter on each column.
I have the following formula at the bottom of column C which gives m
the total count of cells in column C or the total resulting from th
filter applied in Column A =SUBTOTAL(3,C3:C83).

Below that I have a summary
Split No. %
RED 47 58.02%
AMBER 27 33.33%
GREEN 7 8.64%

I need to the results in the No. column of the summary to reflect th
filtered list not the whole spreadsheet - at the moment the formla fo
RED under No is =COUNTIF(C3:C83,"<10") but this counts all cells no
those filtered. Can you help

 

[Bob Phillips]

Try

=SUMPRODUCT((C3:C83<10)*(SUBTOTAL(3,
OFFSET($A$2,ROW($A$3:$A$83)-ROW($A$1),,1))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[raydaw]

Thanks for the help Bob but unfortunately it didn't work. Formula is
still counting all cells in list - even those hidden.

 

[Bob Phillips]

I tested it on my data and it worked, so there must be something in the
data, or my interpretation of it.

Can you post the data, or maybe send me a workbook.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Peo Sjoblom]

Try this

=SUMPRODUCT(--($C$3:$C$83<10),SUBTOTAL(3,OFFSET($A$2,ROW($A$3:$A$83)-MIN(ROW($A$3:$A$83)),,1)))

if that does not work then your values are not what you think, OTOH Bob's
formula should not return all rows. It has a typo in that includes the
header (A2 instead of A3) and

ROW($A$3:$A$83)-ROW($A$1)

should be

ROW($A$3:$A$83)-MIN(ROW($A$3:$A$83)

however it should not count all rows
--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[raydaw]

Thank you both for your help. The formula works great now!

However, here is an additional problem for you. I also need to count
the filtered cells in C3:C83 which have values between (and including)
10 and 16. At present the array formula is
=SUM(IF((F3:F83<=16)-(F3:F83<10),1,0)) which works fine but counts the
hidden rows.

I love your formula but don't understand it well enough to adapt (what
is the purpose of the -- at the start?

Sorry to be thick!

 

[Domenic]

=SUM(IF((F3:F83<=16)-(F3:F83<10),1,0)) which works fine but counts the
hidden rows.
Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F3:F83,ROW(F3:F83)-ROW(F3),0,1)),--(F3:F83>
=10),--(F3:F83<=16))

... what is the purpose of the -- at the start?

Have a look at the following link...

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Hope this helps!

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[raydaw]

HELP - situation is becoming urgent - can anyone tell me how to count
cells with values between 10 and 16 (inclusive) in a filtered list
without including hidden rows

 

[Domenic]

Have you tried the formula I offered?

 

[raydaw]

Dominec

Thank you very much for your help - it works!