# Count a Value When Sorted

Hi!  I simply need to count the number of times "N" appears in a column but have it updated to the accurate count once another column is sorted.  Why can't I figure out what is probably pretty easy?????

11 Replies

# Re: Count a Value When Sorted

Sorting a column does NOT change the count of "N" in the column.

# Re: Count a Value When Sorted

Ok maybe i'm explaining poorly. I have another formula in another cell written as: =SUBTOTAL(3,B5:B104)

This formula will count the number of cells present after sorting. For this new formula, I want to do the same thing where it counts the number of cells but ONLY if the cell reads "N". And I want it do count the cells with "N" even after another column has sorted the table so that the number of cells reading N might now be lower....

# Re: Count a Value When Sorted

I imagine in my poorly trained excel brain that the formula would read: =subtotal(3,J5:J104,IF="N") or something but that's not working obviously...

# Re: Count a Value When Sorted

Hi
Use this formula
=COUNTIF(A1:A22,"N")

# Re: Count a Value When Sorted

I have another formula in another cell written as: =SUBTOTAL(3,B5:B104)

This formula will count the number of cells present after sorting.

No. The SUBTOTAL() result does NOT change after sorting.

As for the function SUBTOTAL() take a look at the online help:

• Function_num     Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

So you might need function_num 103 instead of 3.

# Re: Count a Value When Sorted

@Detlef Lewin ok but for that formula example, it does in fact update the cell count after sorting...  i'm trying to do the same but just only if the letter N is present.  I don't see why I can count cells in general and have them update after sorting but can't count cells that only contain the letter N and have it update after sorting...

# Re: Count a Value When Sorted

I will leave this discussion because you keep mentioning "sorting" despite I told at least twice that there is no connection between sorting and counting.

# Re: Count a Value When Sorted

Thank you. Could you also tell me how I can combine two (or more) IF-Sections. In the case at hand I would like to count the number of the combination of "N" in Column A for example and "Y" in Column B for example.

# Re: Count a Value When Sorted

I guess

``=COUNTIFS(A:A, "N", B:B, "Y")``

# Re: Count a Value When Sorted

@Sergei Baklan Thank you!

# Re: Count a Value When Sorted

@Georges_Vienna , you are welcome