# multiple filtered columns - need to count total in each column

#### LLRiderU

##### New Member
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?

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.

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?

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")

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

=Subtotal(3,B3:B60121)

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!

Replies
10
Views
278
Replies
8
Views
256
Replies
3
Views
486
Replies
2
Views
99
Replies
4
Views
676

1,221,503
Messages
6,160,195
Members
451,630
Latest member
zxhathust

### 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.

### Which adblocker are you using?

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

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