Microsoft excel question on counting rows?
I have a list of insurance companies in column A, a few more columns filled with other information, then a column D filled with "Yes" and "No". I have an autofilter on and I want to count the number of "Yes" and "No" in column D, to get a total number of insurance companies in column A. I want this number to change as the filter is adjusted.
Thanks. Please be descriptive in your answer and if you need more information, I can provide it.
i.e.
1 Insurance CompanyA Yes
2 Insurance CompanyB No
3 Insurance CompanyC Yes
Count: 3 (2 yes + 1 no)
Written by Shakethejake08 in Software
Better Answer
CountA on a range counts cells that have data
e.g. =counta(a1:a50)
counts the number of cells with anything entered between A1 and A50.
However, since you want to only include the values shown instead of the entire range, you cannot use counta directly.
Excel provides a "subtotal" function that can replace specific functions, applying them to filtered results only.
The first argument to subtotal is a magic number from a list that tells Excel which function you want to replace. In your case, counta is number 3 on the list of functions it can replace. (See the pages linked below).
=Subtotal(3,A1:A50)
will count the number of unfiltered cells in the range A1:A50 with values in them. So, replace this range with the range you want to count.
If you have manually hidden any rows that you do not want to count either, use 103 instead of 3 as your function number.
Written by Ruan Caiman
Other Answers
CountA on a range counts cells that have data
e.g. =counta(a1:a50)
counts the number of cells with anything entered between A1 and A50.
However, since you want to only include the values shown instead of the entire range, you cannot use counta directly.
Excel provides a "subtotal" function that can replace specific functions, applying them to filtered results only.
The first argument to subtotal is a magic number from a list that tells Excel which function you want to replace. In your case, counta is number 3 on the list of functions it can replace. (See the pages linked below).
=Subtotal(3,A1:A50)
will count the number of unfiltered cells in the range A1:A50 with values in them. So, replace this range with the range you want to count.
If you have manually hidden any rows that you do not want to count either, use 103 instead of 3 as your function number.
Written by Ruan Caiman