Countif- Visible Cells in Filter mode

[aabbasi]

Hi:

Could someone please let me know the solution for the following problem:

I have values in cell B2:B50 with values such as Quality, Eng, Purchasing etc. When i go in Auto filter based on Coulmn A2:A50 (with July only) and count "Quality" manually under B2:B50, the answer is 26. But if I write formula Countif(B2:B50, "Quality") I get answer 41.

Is there a way to use Countif function, if I am in the Autofilter mode so as it counts only that rows which are visible under Autofilter and not ALL rows.

Thank you

 

[RoryA]

1. Yes.
2. Yes. If you use Worksheetfunction then any error returns a run-time error; if you just use Application then an error value is returned that you can assign to a Variant and test with IsError.

 

[maulana]

WOW.. I like this forum..

 

[RyMonschau13]

Sorry to bump an old topic, but I have a question relating to this...for normal countifs dealing with qualitative data i could put in "=countif(C:C,C2)" and i would then have a count of all the unique entries in column "C". Is there a way I could modify the formula above to do this while filtered?

To give an example I have a list of hospitals, senior centers, rehab clinics, etc. in every state, what I want to do is to filter by state and then do a countif of all the different centers I have in that state, is this possible?

 

[Aladin Akyurek]

Sorry to bump an old topic, but I have a question relating to this...for normal countifs dealing with qualitative data i could put in "=countif(C:C,C2)" and i would then have a count of all the unique entries in column "C". Is there a way I could modify the formula above to do this while filtered?

To give an example I have a list of hospitals, senior centers, rehab clinics, etc. in every state, what I want to do is to filter by state and then do a countif of all the different centers I have in that state, is this possible?

Let K2:K100 house senior centers and the data area be autofiltered by state

Control+shift+enter, not just enter...

=SUM(
  IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(K2,ROW(K2:K100)-ROW(K2),,1)),
  IF(K2:K100<>"",MATCH("~"&K2:K100,K2:K100&"",0))),
   ROW(K2:K100)-ROW(K2)+1),1))

 

[sehested]

Let K2:K100 house senior centers and the data area be autofiltered by state

Control+shift+enter, not just enter...

=SUM(
  IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(K2,ROW(K2:K100)-ROW(K2),,1)),
  IF(K2:K100<>"",MATCH("~"&K2:K100,K2:K100&"",0))),
   ROW(K2:K100)-ROW(K2)+1),1))

I have tried using the following formula to sort my problem, but i cant figure out how to correct it so it fit to my needs.
I have my data fields from I39 to I1200 which consist of wokring days. I use my autofilter to select employees from differnt divisions. Some employees might not have worked that month so the workind days would be zero. If thats the case they shouldnt count as an employee in that month. So i need to countif(I39:I1200,>0) on the visible data only.

 

[Aladin Akyurek]

I have tried using the following formula to sort my problem, but i cant figure out how to correct it so it fit to my needs.
I have my data fields from I39 to I1200 which consist of wokring days. I use my autofilter to select employees from differnt divisions. Some employees might not have worked that month so the workind days would be zero. If thats the case they shouldnt count as an employee in that month. So i need to countif(I39:I1200,>0) on the visible data only.

What is the range housing the employees?

 

[sehested]

What is the range housing the employees?

I have different info about the employees in B39-E1200 where i use autofilter to find the selection of employees i want to look at. Then in column I, M, Q ect (+4 each time) i have working days for each employee. At the top of each column I, M, Q i want it to sum up the total number of active employees.

 

[Aladin Akyurek]

I have different info about the employees in B39-E1200 where i use autofilter to find the selection of employees i want to look at. Then in column I, M, Q ect (+4 each time) i have working days for each employee. At the top of each column I, M, Q i want it to sum up the total number of active employees.

I thought you wanted a distinct count of employees whose workingdays > 0 in an AutoFiltered list. I understand that working days are recorded in I39:I1200. I still don't know where the employees are...

 

[sehested]

I thought you wanted a distinct count of employees whose workingdays > 0 in an AutoFiltered list. I understand that working days are recorded in I39:I1200. I still don't know where the employees are...

Thats also what i want, but i have 4 charastics that and i can autofilter the employees on in column B,C,D,E.

 

[Aladin Akyurek]

Thats also what i want, but i have 4 charastics that and i can autofilter the employees on in column B,C,D,E.

Could you post first 5 rows from the data area?