how do I not count invisible cells
Posted by A Miller on September 04, 2001 11:00 AM
I am using the autofilter feature to narrow down results in several columns. I have the command "=COUNTIF(AA15:AC50,"NG")" in a separate cell to tally all the rows left with "NG" in their final column. The problem is that after I have filtered down the data I only have about 10 "NG"s showing but it still counts the "NG"s from all the rows even though you can't see them. How can I get it to count only those "NG" that survived the filtering and are still visible?
Posted by Russell Hauf on September 04, 2001 12:26 PM
You could use SUBTOTAL.
So your formula would be:
=SUBTOTAL(3,AA15:AC50)
However, this assumes that all of the cells still showing in rows AA, AB, and AC (between rows 15 and 50) all have a value of "NG".
The other way would be without a filter and with a multi-conditional COUNTIF, which Mark W or Aladin could definitely give you a quick answer. (Say you are filtering on rows in column A that have a "2", and then you want to count the "NG" values in columns AA to AC -- you could use one formula with or without filtering. Not sure if that makes much sense, but it can work. Post a sample of what you are trying to do - including the filter stuff - if you are still stuck).
Hope this helps,
Russell
Posted by A Miller on September 04, 2001 1:44 PM
I should have mentioned that my final column can have two possible answers, either a "NG" or a "OK". I am trying to create an analysis that tells me how many "OK"s and how many "NG"s I have left after I have filtered down the rows of data. This is creating a big problem, I would greatly appreciate help with this. I was not able to follow the one formula with or without filtering.
Posted by Aladin Akyurek on September 04, 2001 2:08 PM
It's clear that you want a count, so lets forget the filtering bit.
You have the range AA15:AC50. And you have either a "NG" or "OK" in the cells of the range AC15:AC50.
Now, it should be much easier to tell us what rows should be counted. That is, What conditions has a row to meet in order to be counted?
Would you also tell us what kind of data you have in AA and in AB?
Aladin
