COUNTIF CHALLENGE!
Posted by Inuk on February 05, 2002 9:39 AM
Hi everyone,
I'm trying to get someone`s attention. I sent a message this morning on COUNTIF and Autofilter. Maybe my message was too confusing...Here is take two!
I want to be able to use COUNTIF() in a very wide(over 50 columns) Autofiltered table.
But:
(1)COUNTIF()is not bright enough to countif only cells in visible-filtered rows,
(2)SUBTOTAL()does not have COUNTIF() as an embedded subfunction,
(3)SUBTOTAL()does not behave like other functions(ex.SUM(), COUNT()) when you try to use it as a CSE(array formula),
(4)I could probably build a complex CSE formula with multiple IFs and get rid of the Autofilter, but then why does Excel have an Autofilter in the first place?
I hope this will get someone`s attention!
Inuk
Posted by Juan Pablo G. on February 05, 2002 9:44 AM
I don't understand something, if you're autofiltering, what can you filter the IF in COUNTIF ? then you could Use SUBTOTALS(3,)
Juan Pablo G.
Posted by Inuk on February 05, 2002 9:57 AM
I cannot use SUBTOTALS(3,) in this case because I only want to count certain values, for example, that are over a criteria value.
I use the Autofilter to select specific datasets based on other criteria in other columns like A$:River, B$:Location, C$:Date, etc...and countif on the resulting values in other columns like G$:Arsenic, H$:Lead...etc but I only want to know how many in each column respect a certain criteria (ex. How many Arsenic readings are over 0.01)...
I can send a sample version by e-mail to illustrate how my spreadsheet is built..
Inuk
Posted by Mark W. on February 05, 2002 9:59 AM
He's wants to filter on a column and count given items in a 2nd column...
My recommendation is to add a column with the
count condition coerced into a numeric value
(e.g., =(A2="apples")+0), and then use
=SUBTOTAL(9,ref) on that new column.
Posted by Mark W. on February 05, 2002 10:00 AM
See my posting above [NT]
Posted by Inuk on February 05, 2002 10:56 AM
Re: See my posting above [NT]
I might be wrong, but I don`t think you quite inderstand what I'm trying to do. I know it`s sometimes hard to explain in a few words by email...
I tried the suggestion with =(A2="apples")+0,
but was not able to get anything understandable!
Here is a simplified example. AUTOFILTER is on multiple columns (A trough D in the example below) and COUNTIF has to be on each following collumns (E trough H in the example below) and has to "countif" based on the CRITERIA variable. I hope this shows well when I post on the list...
Example:
COLA COLB COLC COLD COLE COLF COLG COLH
CODE RIVER SITE DATE STATS AAAAAA BBBBB CCCCC
MIN SUB(5) SUB(5) SUB(5)
AVE SUB(1) SUB(1) SUB(1)
MAX SUB(4) SUB(4) SUB(4)
COUNT SUB(2) SUB(2) SUB(2)
COUNTIF ????? ????? ?????
CRITERIA 0.04 10 0.010
>>>>>>>>(FILTER ON THIS LINE)<<<<<<<<<<<<<<<<<<<<br>A PETA 1 Nov1 0.01 10 0.005
A PETA 1 Nov5 0.05 15 0.004
B PETA 2 Nov1 0.03 20 0.025
B PETA 2 Nov5 0.05 14 0.081
C ALAS 1 Nov1 0.07 8 0.064
D ALAS 2 Nov1 0.06 9 0.001
Inuk
Posted by Mark W. on February 05, 2002 11:04 AM
Explain your criteria [NT]
Posted by Inuk on February 05, 2002 11:19 AM
Criteria = numerical value
Criteria is a numerical value, threshold, maximum or minimum value for a field reading. Ex. Arsenic should not read over 0.01. Criteria are different for each Measured Substance column..
Here is the example again but formated with Underscores!
Inuk
Example:
COLA COLB__COLC COLD COLE____COLF___COLG___COLH
CODE RIVER SITE DATE STATS___AAAAAA_BBBBB__CCCCCC
_____________________MIN_____SUB(5) SUB(5) SUB(5)
_____________________AVE_____SUB(1) SUB(1) SUB(1)
_____________________MAX_____SUB(4) SUB(4) SUB(4)
_____________________COUNT___SUB(2) SUB(2) SUB(2)
_____________________COUNTIF ????? ?????___?????
_____________________CRITERIA 0.04 ___10___0.010
>>>>>>>>(FILTER ON THIS LINE)<<<<<<<<<<<<<<<<<<<<br>A ___PETA __1 __Nov1 _________0.01 ___10 __0.005
A ___PETA __1 __Nov5 _________0.05 ___15 __0.004
B ___PETA __2 __Nov1 _________0.03 ___20 __0.025
B ___PETA __2 __Nov5 _________0.05 ___14 __0.081
C ___ALAS __1 __Nov1 _________0.07 ___08 __0.064
D ___ALAS __2 __Nov1 _________0.06 ___09 __0.001
Posted by Mark W. on February 05, 2002 11:30 AM
AND-ed, OR-ed, or what? [NT]
Posted by Mark W. on February 05, 2002 11:37 AM
...and, using which comparison operators? [NT]
Posted by Inuk on February 05, 2002 11:43 AM
Ì'm not sure what you mean?
I'm not sure what your getting at but for example, Arsenic has to be lower than 0.04 if I filter for any CODE,RIVER,SITE and DATE...
Does that help?
Inuk
Posted by Mark W. on February 05, 2002 11:55 AM
Re: Ì'm not sure what you mean?
In the formula, =COUNTIF(range,"<>apples"),
"<>apples" is the criteria. It employes a
not equal comparison operator.
How do you want your column E thru H values
compared to {0.04,10,0.01}?
Posted by Inuk on February 05, 2002 3:41 PM
Criteria
I want a function in column E that will count every (filtered) occurence in Column E that are over 0.04, and one(function) in column F that will count every (filtered)occurence in Column F that are over 10 and one in column G that will count every (filtered) occurence in Column G that are over 0.01, etc...You get the point...
I thought Countif() would be great to do that if it would only be intelligent enough to realize I'm filtering below the Countif() cell...
Can I send you a sample file...it would save alot of explaining?
inuk@nbnet.nb.ca
Posted by Inuk on February 06, 2002 7:09 AM
Gave up so soon?
Posted by John Beaudoin on February 06, 2002 11:54 AM
Don't use the autofilter. Use the advanced filter and post the list to a new location. If you need to do this a couple of times for more than one filter, you can do it. When the sorted list copies to the new location, it does not take the hidden cells with it, and the count function works properly.
Posted by Mark W. on February 06, 2002 11:59 AM
Nope, Tuesday is my billiards league night!...
I don't really need to see your worksheet... I
need to understand your logic. Can you verbalize
the exact nature of the comparison that you
want to perform on the data set that your provided.
