Results 1 to 5 of 5

Thread: Formula merging a countif with a second if statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula merging a countif with a second if statement

    Sorry for the vague description but not sure how best to describe this. Here is the situation below. I want column C to be a formula. Out of all of the occurrences of Oranges in column B, if any of them have a value of 5 or above in column A, then I want it to return a Y in column C for all occurrences of Oranges, if not I want it to return an N. Thanks in advance.... Same logic for Pears, Mangos or any other fruit that appears in column B

    A B C
    4 oranges y
    3 oranges y
    2 oranges y
    2 oranges y
    1 oranges y
    5 oranges y
    2 oranges y
    4 apples n
    2 apples n
    4 apples n
    1 apples n
    3 pears n
    4 mango y
    1 mango y
    5 mango y
    5 mango y
    3 mango y
    Last edited by bluefish44; Jul 29th, 2019 at 05:41 PM.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula merging a countif with a second if statement

    If you have Excel 365 with the MAXIFS function, try the C2 formula. Otherwise, try the D2 formula.

    ABCD
    1ABCC
    24orangesyy
    33orangesyy
    42orangesyy
    52orangesyy
    61orangesyy
    75orangesyy
    82orangesyy
    94applesnn
    102applesnn
    114applesnn
    121applesnn
    133pearsnn
    144mangoyy
    151mangoyy
    165mangoyy
    175mangoyy
    183mangoyy

    Sheet2



    Worksheet Formulas
    CellFormula
    C2=IF(MAXIFS(A:A,B:B,B2)>=5,"y","n")
    D2=IF(AGGREGATE(14,6,$A$2:$A$18/($B$2:$B$18=B2),1)>=5,"y","n")

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Formula merging a countif with a second if statement

    Other way:

     ABC
    1ABC
    24orangesy
    33orangesy
    42orangesy
    52orangesy
    61orangesy
    75orangesy
    82orangesy
    94applesn
    102applesn
    114applesn
    121applesn
    133pearsn
    144mangoy
    151mangoy
    165mangoy
    175mangoy
    183mangoy

    CellFormula
    C2=IF(COUNTIFS($B$2:$B$18,B2,$A$2:$A$18,">=5")>0,"y","n")
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula merging a countif with a second if statement

    Hi,

    Almost the same as Dante's:

    ABC
    14orangesy
    23orangesy
    32orangesy
    42orangesy
    51orangesy
    65orangesy
    72orangesy
    84applesn
    92applesn
    104applesn
    111applesn
    123pearsn
    134mangoy
    141mangoy
    155mangoy
    165mangoy
    173mangoy

    Sheet698



    Worksheet Formulas
    CellFormula
    C1=IF(COUNTIFS(A$1:A$17,">4",B$1:B$17,B1),"y","n")



    Change adjust cell references/range as needed, formula copied down.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Formula merging a countif with a second if statement

    Quote Originally Posted by jtakw View Post
    Hi,


    Worksheet Formulas
    Cell Formula
    C1 =IF(COUNTIFS(A$1:A$17,">4",B$1:B$17,B1),"y","n")

    Change adjust cell references/range as needed, formula copied down.
    After putting the formula, I noticed that the > 0 is not necessary, but when I was going to make the change, your answer was already there.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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