multiple filtered columns - need to count total in each column

LLRiderU

New Member
Joined
Mar 28, 2013
Messages
5
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?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

LLRiderU

New Member
Joined
Mar 28, 2013
Messages
5
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

New Member
Joined
Mar 28, 2013
Messages
5
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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

New Member
Joined
Mar 28, 2013
Messages
5
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Use subtotal but instead of 2 for count for use 3 for counta



=Subtotal(3,B3:B60121)
 

LLRiderU

New Member
Joined
Mar 28, 2013
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,529
Messages
5,659,343
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top