| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count entries in one column based on values in another column | Excel Worksheet Functions | |||
| How to count certain values in a column | Excel Discussion (Misc queries) | |||
| Count Text Values in Column | Excel Discussion (Misc queries) | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Can I count values in column 1 if criteria in column 2 are met | Excel Worksheet Functions | |||
Linear Mode
