Results 1 to 6 of 6
Thread: formula for criteria (2000 sr1)

20021111, 05:18 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
formula for criteria (2000 sr1)
Hi all,
I was wondering if there was a way for the criteria in a COUNTIF (and SUMIF and maybe even for a Conditional Format) to be a function. What I would like to do is something like the following:
=COUNTIF(a1:a12,OR("yes","y"))
that is, count the cell if it has "yes" or "y" in it. I know how to do this as an array formula (and VBA) but COUNTIF would be easier to read if someone unfamiliar with array formulas is looking at my spreadsheet.
I tried many variants of the above. The best I got was an answer of 0 even tho some items in the range were equal to "yes" or "y". Worst I got was all kinds of errors.
The same kind of question holds for SUMIF and for the criteria for Conditional Formatting (if I wanted to look at >3 conditions, not the case here).
COUNTIF does allow some functions but apparently not others. I could do
=COUNTIF(a1:a12,MAX(a1,a12))
Thanks much.
Fred

20021111, 05:34 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: formula for criteria (2000 sr1)
To keep the formula simple, why not use:
=COUNTIF(a1:a12,"y")+COUNTIF(a1:a12,"yes")John ... I float in liquid gardens
UTC 7ąDS

20021111, 07:49 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,343
 Thanks
 3
 Thanked 222 Times in 205 Posts
Re: formula for criteria (2000 sr1)
Hi Fred,
The reason you're getting a 0 is because none of your cells match what the OR function is returning, namely True or False. For the example you gave, assuming you're trying to pick out Yes variations in a column that has variations of yes and no, you could use =Countif(A1:A12,"y*") if that helps?Regards,
Rory
Microsoft MVP  Excel

20021111, 12:17 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: formula for criteria (2000 sr1)
Hi Rory,
Thanks for the tip. I didn't know you could use * as a wildcard in a constant like that. What if you really wanted to match "y*" (w/o quotes)? I'd assume there'd be a wildcard escape.
I was hoping for a single solution that would take care of all my "yes" needs. There are 3 of them. Let me explain what I was doing.
I put together a meeting scheduling form for our secretary. Fill in names and indicate availability for a meeting at a particular date/time. The 3 places where I needed to be concerned with the form of "yes" entered are as follows:
1. for each person invited to the meeting, if they are critical to the meeting (can't do it if they're not there), I have a col (col C) where our secretary enters "yes" or some form of it (leaves blank if not critical).
2. I want a count of the # critical people invited
3. for each date/time, I want to be able to show if all the critical people are available
for 1, I want to color the cell red if she enters "yes". Plain old conditional formatting. But how to allow the various forms of "yes"? OK, use 2 conditions (one for "yes" and one for "y" and hope she doesn't enter "ye" (ignoring autocomplete for the moment)). I was hoping the solution to the original post would work here as well. "y*" did NOT work for a single condition. I also tried defining a name called "yes_forms" equal to "y*" but that didn't work here either. So had to stay with 2 conditions. Issue, as in original email, is what if there's >3 possible entries?
for 2, your solution certainly did work as did "yes_forms". JohnBF's would have worked as well. The cell where this is totalled is named number_critical (see 3 below)
for 3, I have the following array formula for col G, which is for a particular date/time and similar formulas for each date/time col. If the person is available, I entered "ok" (maybe should have stayed with "yes" or some form).
{=IF(SUM((G8:G25="ok")*(($C8:$C25="yes")+($C8:$C25 ="y")))=number_critical,"yes","no")}
So what this formula does is, for each of rows 8:25 (each row being a person invited to the meeting), checks whether they were ok for the date/time and whether they were critical ("yes" or "y"), sums up the rows where the critical people are available, and compares to the total number of critical people invited. If that comparison is equal, enter a "yes" (meaning all critical people are available for the date/time); otherwise enters a "no". (Just as an aside, I also conditional format this cell based on "yes" so the date/time cols where all critical people are available are easy to see.) It would have been nice if I didn't have to spell out each form of "yes" here. Tried "y*" and my "yes_forms" name  neither worked.
Note the use of + in above for an OR condition. That didn't work for 1 either.
So that's it. I guess 1 shoe doesn't fit all.
Thanks again.
Fred

20021111, 12:19 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: formula for criteria (2000 sr1)
John,
Thks for the suggestion. Simple is always good.
See response to Rory for a full explanation of what I was doing. I guess COUNTIF's criteria is the most flexible.
Fred

20021111, 18:40 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,343
 Thanks
 3
 Thanked 222 Times in 205 Posts
Re: formula for criteria (2000 sr1)
Fred,
Have you tried Data Validation  i.e. only allowing them Yes, No and Blank as possible values? Makes the formula a lot easier to deal with!Regards,
Rory
Microsoft MVP  Excel