multiple filtered columns - need to count total in each column

[LLRiderU]

Hello. I have a spreadsheet that has multiple columns and I need to filter different terms for each column. For example, the first column "ID" contains student ID numbers that are 7 digits. Another column is a answers to question about each student, so the data in that column will contain either a Y for Yes or N for No, or be blank if it hasn't been answered yet. The spreadsheet has 1885 total entries. I want a count of total entries in each column after I filter it. For example, if I filter the ID column and exclude one ID, the count will go from 1885 down to 1884.

I also have a count function at the top of Y or N column. My problem is that when I filter the ID column and the count goes down in that column, it doesn't go down in the Y or N column. It's not removing the filtered out info from the count total in the Y or N column.

Is there a formula that can fix this?

 

[schielrn]

Welcome to the Board!

Have a look at subtotal function for count:

SUBTOTAL function - Excel - Office.com

Hope that helps and post back with any questions in regards to the function.

 

[LLRiderU]

thanks! I do have the subtotal formula for count already set up. however, my issue is that when i filter data out of one column, the count on the other column doesn't go down as it should (the subtotal count isn't removing the filtered out info). does that make sense?

 

[LLRiderU]

sorry - don't think i'm explaining it correctly. the subtotal count function works on the column with the student ids. but it doesn't work on the column with the Y or N. I have a countIf function in there, but when i filter out the Y the total doesn't go down.

 

[schielrn]

I assume the student IDs are numbers and that is probably why the count is working with those and not the Y/N column. Use counta which is option 3 instead of option 2.

Does that work?

 

[LLRiderU]

Correct, the student IDs are numbers. This is the formula I tried, but it didn't work. Maybe I did not enter correctly? I'm relatively new to excel...and REALLY appreciate your help!

=COUNTA(3,B3:B60121,"Y")

 

[schielrn]

Use subtotal but instead of 2 for count for use 3 for counta



=Subtotal(3,B3:B60121)

 

[LLRiderU]

Thank you SO much! It looks like it worked! I'm going to run it by my boss and make sure that's exactly the result she was looking for. I'll post back if it's something else, but for now - THANK YOU! I'm so glad I joined this board - this is amazing!