Question: About using COUNTIF in a filtered list

[Guest]

Looking for some help with a filtered list. What I have is a sheet full of project jobs, each has a sales person assigned. I am filtering the list by sales person name (with Auto Filter). One column on the sheet is a status value (won or lost). I want to be able to count each value, when sorted. I am trying to use COUNTIF to do this, but it doesn't see to recognize when I use the filter. It still shows the total value. EX: =COUNTIF(B9:B9999,"Won"

Does anyone know how to use COUNTIF so it will ignore hidden rows that result from a list being filtered. This is the result you see when using the SUBTOTAL function; which operates on the visible data that results from a list that has been filtered. Is it possible to nest these two functions? I can't seem to get the syntax right

THANKS!

 

[Guest]

Scot

If you filter both for sales rep and won/lost, then you can use the SUBTOTAL(3,bb1000) to get the counts

Another option is to use the SUMPRODUCT command. If you have a data validation cell with a unique list of the sales reps, you can use that list to isolate the sales rep that you require. You could then use SUMPRODUCT(--(a9:a1000 = selectedrep),--(b9:b1000="won")) to get the counts. I don't know if you can extract the sales rep from a filtered list and put it into a separate cell rather than generating a unique list of the sales reps

Ton

----- Scott wrote: ----

Looking for some help with a filtered list. What I have is a sheet full of project jobs, each has a sales person assigned. I am filtering the list by sales person name (with Auto Filter). One column on the sheet is a status value (won or lost). I want to be able to count each value, when sorted. I am trying to use COUNTIF to do this, but it doesn't see to recognize when I use the filter. It still shows the total value. EX: =COUNTIF(B9:B9999,"Won"

Does anyone know how to use COUNTIF so it will ignore hidden rows that result from a list being filtered. This is the result you see when using the SUBTOTAL function; which operates on the visible data that results from a list that has been filtered. Is it possible to nest these two functions? I can't seem to get the syntax right

THANKS!

 

[Guest]

Tony

Thanks for the quick response. I think I didn't do a good job explaining what I needed. I'm a novice with functions, so please forgive me for any confusion.

More of what I'm looking to do is compare "Won" jobs to "Lost" jobs to calculate a hit rate. By filtering my table I can see how sales rep "A" does, or how sales rep "B" does. Actually filtering by sales rep is just one dynamic. I also may look at the data by customer, date, etc. Make sense? Right now, all I can get is the hit rate for the entire list no matter my filter choices. (If it would help, I can email you the workbook I have up to this point.

Hope that clarifies a bit, and that you're willing to share your thoughts. Thanks in advance

Sincerely

Scot

----- acw wrote: ----

Scot

If you filter both for sales rep and won/lost, then you can use the SUBTOTAL(3,bb1000) to get the counts

Another option is to use the SUMPRODUCT command. If you have a data validation cell with a unique list of the sales reps, you can use that list to isolate the sales rep that you require. You could then use SUMPRODUCT(--(a9:a1000 = selectedrep),--(b9:b1000="won")) to get the counts. I don't know if you can extract the sales rep from a filtered list and put it into a separate cell rather than generating a unique list of the sales reps


Ton

----- Scott wrote: ----

Looking for some help with a filtered list. What I have is a sheet full of project jobs, each has a sales person assigned. I am filtering the list by sales person name (with Auto Filter). One column on the sheet is a status value (won or lost). I want to be able to count each value, when sorted. I am trying to use COUNTIF to do this, but it doesn't see to recognize when I use the filter. It still shows the total value. EX: =COUNTIF(B9:B9999,"Won"

Does anyone know how to use COUNTIF so it will ignore hidden rows that result from a list being filtered. This is the result you see when using the SUBTOTAL function; which operates on the visible data that results from a list that has been filtered. Is it possible to nest these two functions? I can't seem to get the syntax right

THANKS!

 

[Guest]

Scot

Send a copy of the workbook to (e-mail address removed). I'll have a look

Ton

----- Scott wrote: ----

Tony

Thanks for the quick response. I think I didn't do a good job explaining what I needed. I'm a novice with functions, so please forgive me for any confusion.

More of what I'm looking to do is compare "Won" jobs to "Lost" jobs to calculate a hit rate. By filtering my table I can see how sales rep "A" does, or how sales rep "B" does. Actually filtering by sales rep is just one dynamic. I also may look at the data by customer, date, etc. Make sense? Right now, all I can get is the hit rate for the entire list no matter my filter choices. (If it would help, I can email you the workbook I have up to this point.

Hope that clarifies a bit, and that you're willing to share your thoughts. Thanks in advance

Sincerely

Scot

----- acw wrote: ----

Scot

If you filter both for sales rep and won/lost, then you can use the SUBTOTAL(3,bb1000) to get the counts

Another option is to use the SUMPRODUCT command. If you have a data validation cell with a unique list of the sales reps, you can use that list to isolate the sales rep that you require. You could then use SUMPRODUCT(--(a9:a1000 = selectedrep),--(b9:b1000="won")) to get the counts. I don't know if you can extract the sales rep from a filtered list and put it into a separate cell rather than generating a unique list of the sales reps


Ton

----- Scott wrote: ----

Looking for some help with a filtered list. What I have is a sheet full of project jobs, each has a sales person assigned. I am filtering the list by sales person name (with Auto Filter). One column on the sheet is a status value (won or lost). I want to be able to count each value, when sorted. I am trying to use COUNTIF to do this, but it doesn't see to recognize when I use the filter. It still shows the total value. EX: =COUNTIF(B9:B9999,"Won"

Does anyone know how to use COUNTIF so it will ignore hidden rows that result from a list being filtered. This is the result you see when using the SUBTOTAL function; which operates on the visible data that results from a list that has been filtered. Is it possible to nest these two functions? I can't seem to get the syntax right

THANKS!

 

[Debra Dalgleish]

You could use a pivot table to summarize and filter the data. There are
instructions and links here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm