ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Count Text Values in a filterd column (https://www.excelbanter.com/excel-worksheet-functions/136043-count-text-values-filterd-column.html)

Telecom
Count Text Values in a filterd column
 
I need a running count at the top of a column of single text vaules in a
column once sheet has ben filtered.

eg: need running count of number of W's if I filter by pairs 1 to 5 only.

Cable Pair # Type Status
NHI 1 1 ** W
NHI 1 2 ** W
NHI 1 3 ** W
NHI 1 4 ** S
NHI 1 5 ** W
NHI 1 6 ** W
NHI 1 7 ** F
NHI 1 8 ** S
NHI 1 9 ** W
NHI 1 10 ** S
NHI 1 11 ** F
NHI 1 12 ** W
NHI 1 13 ** S
NHI 1 14 ** S
NHI 1 15 ** S
NHI 1 16 ** S
NHI 1 17 ** W
NHI 1 18 ** S
NHI 1 19 ** S
NHI 1 20 ** W
NHI 1 21 ** S
NHI 1 22 ** S
NHI 1 23 ** S
NHI 1 24 ** S
NHI 1 25 ** S
NHI 1 26 ** S
NHI 1 27 ** S
NHI 1 28 ** S
NHI 1 29 ** S
NHI 1 30 ** W
NHI 1 31 ** W
NHI 1 32 ** W
NHI 1 33 ** S
NHI 1 34 ** S

Bob Phillips
Count Text Values in a filterd column
 
=SUMPRODUCT(SUBTOTAL(3,OFFSET(E1,ROW($E$2:$E$35)-ROW($E$1),,1)),--($E$2:$E$35="W"))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Telecom" wrote in message
...
I need a running count at the top of a column of single text vaules in a
column once sheet has ben filtered.

eg: need running count of number of W's if I filter by pairs 1 to 5 only.

Cable Pair # Type Status
NHI 1 1 ** W
NHI 1 2 ** W
NHI 1 3 ** W
NHI 1 4 ** S
NHI 1 5 ** W
NHI 1 6 ** W
NHI 1 7 ** F
NHI 1 8 ** S
NHI 1 9 ** W
NHI 1 10 ** S
NHI 1 11 ** F
NHI 1 12 ** W
NHI 1 13 ** S
NHI 1 14 ** S
NHI 1 15 ** S
NHI 1 16 ** S
NHI 1 17 ** W
NHI 1 18 ** S
NHI 1 19 ** S
NHI 1 20 ** W
NHI 1 21 ** S
NHI 1 22 ** S
NHI 1 23 ** S
NHI 1 24 ** S
NHI 1 25 ** S
NHI 1 26 ** S
NHI 1 27 ** S
NHI 1 28 ** S
NHI 1 29 ** S
NHI 1 30 ** W
NHI 1 31 ** W
NHI 1 32 ** W
NHI 1 33 ** S
NHI 1 34 ** S

Toppers
Count Text Values in a filterd column
 
Assuming data is in columns A to D:

=SUMPRODUCT(--(B2:B100)<=5),--(D2:D100="W"))

will give the required result (and no need to filter)

You could change the 5 to a cell reference containing your max pair value

HTH

"Telecom" wrote:

I need a running count at the top of a column of single text vaules in a
column once sheet has ben filtered.

eg: need running count of number of W's if I filter by pairs 1 to 5 only.

Cable Pair # Type Status
NHI 1 1 ** W
NHI 1 2 ** W
NHI 1 3 ** W
NHI 1 4 ** S
NHI 1 5 ** W
NHI 1 6 ** W
NHI 1 7 ** F
NHI 1 8 ** S
NHI 1 9 ** W
NHI 1 10 ** S
NHI 1 11 ** F
NHI 1 12 ** W
NHI 1 13 ** S
NHI 1 14 ** S
NHI 1 15 ** S
NHI 1 16 ** S
NHI 1 17 ** W
NHI 1 18 ** S
NHI 1 19 ** S
NHI 1 20 ** W
NHI 1 21 ** S
NHI 1 22 ** S
NHI 1 23 ** S
NHI 1 24 ** S
NHI 1 25 ** S
NHI 1 26 ** S
NHI 1 27 ** S
NHI 1 28 ** S
NHI 1 29 ** S
NHI 1 30 ** W
NHI 1 31 ** W
NHI 1 32 ** W
NHI 1 33 ** S
NHI 1 34 ** S

Telecom
Count Text Values in a filterd column
 
Thanks Toppers

But when I write
=SUMPRODUCT(--(B6:B3000)<=H3)--(D6:D3000="w")

It won't except the last bracket and returns an error.

Is there also a way of setting a max and min pair range?

Cheers

"Toppers" wrote:

Assuming data is in columns A to D:

=SUMPRODUCT(--(B2:B100)<=5),--(D2:D100="W"))

will give the required result (and no need to filter)

You could change the 5 to a cell reference containing your max pair value

HTH

"Telecom" wrote:

I need a running count at the top of a column of single text vaules in a
column once sheet has ben filtered.

eg: need running count of number of W's if I filter by pairs 1 to 5 only.

Cable Pair # Type Status
NHI 1 1 ** W
NHI 1 2 ** W
NHI 1 3 ** W
NHI 1 4 ** S
NHI 1 5 ** W
NHI 1 6 ** W
NHI 1 7 ** F
NHI 1 8 ** S
NHI 1 9 ** W
NHI 1 10 ** S
NHI 1 11 ** F
NHI 1 12 ** W
NHI 1 13 ** S
NHI 1 14 ** S
NHI 1 15 ** S
NHI 1 16 ** S
NHI 1 17 ** W
NHI 1 18 ** S
NHI 1 19 ** S
NHI 1 20 ** W
NHI 1 21 ** S
NHI 1 22 ** S
NHI 1 23 ** S
NHI 1 24 ** S
NHI 1 25 ** S
NHI 1 26 ** S
NHI 1 27 ** S
NHI 1 28 ** S
NHI 1 29 ** S
NHI 1 30 ** W
NHI 1 31 ** W
NHI 1 32 ** W
NHI 1 33 ** S
NHI 1 34 ** S

Toppers
Count Text Values in a filterd column
 
my error ....bracket in wrong place ....

=SUMPRODUCT(--(B6:B3000<=H3)--(D6:D3000="w"))

HTH


"Telecom" wrote:

Thanks Toppers

But when I write
=SUMPRODUCT(--(B6:B3000)<=H3)--(D6:D3000="w")

It won't except the last bracket and returns an error.

Is there also a way of setting a max and min pair range?

Cheers

"Toppers" wrote:

Assuming data is in columns A to D:

=SUMPRODUCT(--(B2:B100)<=5),--(D2:D100="W"))

will give the required result (and no need to filter)

You could change the 5 to a cell reference containing your max pair value

HTH

"Telecom" wrote:

I need a running count at the top of a column of single text vaules in a
column once sheet has ben filtered.

eg: need running count of number of W's if I filter by pairs 1 to 5 only.

Cable Pair # Type Status
NHI 1 1 ** W
NHI 1 2 ** W
NHI 1 3 ** W
NHI 1 4 ** S
NHI 1 5 ** W
NHI 1 6 ** W
NHI 1 7 ** F
NHI 1 8 ** S
NHI 1 9 ** W
NHI 1 10 ** S
NHI 1 11 ** F
NHI 1 12 ** W
NHI 1 13 ** S
NHI 1 14 ** S
NHI 1 15 ** S
NHI 1 16 ** S
NHI 1 17 ** W
NHI 1 18 ** S
NHI 1 19 ** S
NHI 1 20 ** W
NHI 1 21 ** S
NHI 1 22 ** S
NHI 1 23 ** S
NHI 1 24 ** S
NHI 1 25 ** S
NHI 1 26 ** S
NHI 1 27 ** S
NHI 1 28 ** S
NHI 1 29 ** S
NHI 1 30 ** W
NHI 1 31 ** W
NHI 1 32 ** W
NHI 1 33 ** S
NHI 1 34 ** S

Toppers
Count Text Values in a filterd column
 
...oh dear ....bad day at the office ....

=SUMPRODUCT(--(B6:B3000<=H3),--(D6:D3000="w"))


"Toppers" wrote:

my error ....bracket in wrong place ....

=SUMPRODUCT(--(B6:B3000<=H3)--(D6:D3000="w"))

HTH


"Telecom" wrote:

Thanks Toppers

But when I write
=SUMPRODUCT(--(B6:B3000)<=H3)--(D6:D3000="w")

It won't except the last bracket and returns an error.

Is there also a way of setting a max and min pair range?

Cheers

"Toppers" wrote:

Assuming data is in columns A to D:

=SUMPRODUCT(--(B2:B100)<=5),--(D2:D100="W"))

will give the required result (and no need to filter)

You could change the 5 to a cell reference containing your max pair value

HTH

"Telecom" wrote:

I need a running count at the top of a column of single text vaules in a
column once sheet has ben filtered.

eg: need running count of number of W's if I filter by pairs 1 to 5 only.

Cable Pair # Type Status
NHI 1 1 ** W
NHI 1 2 ** W
NHI 1 3 ** W
NHI 1 4 ** S
NHI 1 5 ** W
NHI 1 6 ** W
NHI 1 7 ** F
NHI 1 8 ** S
NHI 1 9 ** W
NHI 1 10 ** S
NHI 1 11 ** F
NHI 1 12 ** W
NHI 1 13 ** S
NHI 1 14 ** S
NHI 1 15 ** S
NHI 1 16 ** S
NHI 1 17 ** W
NHI 1 18 ** S
NHI 1 19 ** S
NHI 1 20 ** W
NHI 1 21 ** S
NHI 1 22 ** S
NHI 1 23 ** S
NHI 1 24 ** S
NHI 1 25 ** S
NHI 1 26 ** S
NHI 1 27 ** S
NHI 1 28 ** S
NHI 1 29 ** S
NHI 1 30 ** W
NHI 1 31 ** W
NHI 1 32 ** W
NHI 1 33 ** S
NHI 1 34 ** S


All times are GMT +1. The time now is 09:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com