Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  2. #2
    Uranium Lounger
    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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •