Count values in column but exclude filtered rows

**elee532** · 07-24-2013, 09:57 PM

I am trying to count the number of times that any one of the following values occurs in a column: "1" "2" "2i" or "P"

I tried using =COUNTIF(BD2:BD45,"P")+COUNTIF(BD2:BD45,"2")+COUNTIF(BD2:BD45,"2!")+COUNTIF(BD2:BD45,"1"). However, if I apply a filter to one of my other columns, the result does not change to reflect the filtered out rows.

How can I make this work?

I am using Excel 2013.

Thanks.

**FDibbins** · 07-25-2013, 12:26 AM

Hi and welcome to the forum

If you are using 2013, please update your profile accordingly.

Take a look at using the subtotal() function, there are many options under that

**elee532** · 07-25-2013, 04:12 AM

I still can't seem to figure it out. I would be grateful for any further detail you could offer. Thank you!!

**:) Sixthsense :)** · 07-25-2013, 04:57 AM

Whether arriving the result using a helper column is ok for you?

**elee532** · 07-25-2013, 10:27 AM

I'm not sure how/whether it would be possible to use a helper column. I need to run this calculation on about 100 different columns. To elaborate a little bit, I have a row of student names and a column for each day that they should have attended class. There are 5 different possible values in this column (1, 2, 2i, P, and A). I just need a total of how many times 1, 2, 2i, or P occurs in each column. This would tell me Total Daily Attendance which I use to compute an Average Daily Attendance for the month). Now, if I filter on gender=female, I want the Daily Attendance and Average Daily Attendance calculation to reflect just the attendance for female.

I attached some sample data if it will help.

Thanks!