counting cells in filtered list

[raydaw]

I have a 3 column spreadsheet, the third column is conditionally formatted and the spreadsheet has an automatic filter on each column.
I have the following formula at the bottom of column C which gives me the total count of cells in column C or the total resulting from the 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 the filtered list not the whole spreadsheet - at the moment the formla for RED under No is =COUNTIF(C3:C83,"<10") but this counts all cells not 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" <raydaw.25hj0m_1143726001.1615@excelforum-nospam.com> wrote in
message news:raydaw.25hj0m_1143726001.1615@excelforum-nospam.com...
>
> I have a 3 column spreadsheet, the third column is conditionally
> formatted and the spreadsheet has an automatic filter on each column.
> I have the following formula at the bottom of column C which gives me
> the total count of cells in column C or the total resulting from the
> 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 the
> filtered list not the whole spreadsheet - at the moment the formla for
> RED under No is =COUNTIF(C3:C83,"<10") but this counts all cells not
> those filtered. Can you help?
>
>
> --
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
http://www.excelforum.com/member.php...o&userid=32982
> View this thread: http://www.excelforum.com/showthread...hreadid=528087
>

[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)

"raydaw" <raydaw.25hqw0_1143736204.6819@excelforum-nospam.com> wrote in
message news:raydaw.25hqw0_1143736204.6819@excelforum-nospam.com...
>
> Thanks for the help Bob but unfortunately it didn't work. Formula is
> still counting all cells in list - even those hidden.
>
>
> --
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
http://www.excelforum.com/member.php...o&userid=32982
> View this thread: http://www.excelforum.com/showthread...hreadid=528087
>

[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" <raydaw.25hqw0_1143736204.6819@excelforum-nospam.com> wrote in
message news:raydaw.25hqw0_1143736204.6819@excelforum-nospam.com...
>
> Thanks for the help Bob but unfortunately it didn't work. Formula is
> still counting all cells in list - even those hidden.
>
>
> --
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
> http://www.excelforum.com/member.php...o&userid=32982
> View this thread: http://www.excelforum.com/showthread...hreadid=528087
>

[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]

In article <raydaw.25oxga_1144071301.3755@excelforum-nospam.com>,
raydaw <raydaw.25oxga_1144071301.3755@excelforum-nospam.com> wrote:

> =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" <raydaw.25oxga_1144071301.3755@excelforum-nospam.com> wrote in
message news:raydaw.25oxga_1144071301.3755@excelforum-nospam.com...
>
> 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!
>
>
> --
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
http://www.excelforum.com/member.php...o&userid=32982
> View this thread: http://www.excelforum.com/showthread...hreadid=528087
>

[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?

In article <raydaw.25qgry_1144143000.6184@excelforum-nospam.com>,
raydaw <raydaw.25qgry_1144143000.6184@excelforum-nospam.com> wrote:

> 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

[raydaw]

Dominec

Thank you very much for your help - it works!: