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

1. ## 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

2. ## 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")

3. ## Re: Formula merging a countif with a second if statement

Other way:

 A B C 1 A B C 2 4 oranges y 3 3 oranges y 4 2 oranges y 5 2 oranges y 6 1 oranges y 7 5 oranges y 8 2 oranges y 9 4 apples n 10 2 apples n 11 4 apples n 12 1 apples n 13 3 pears n 14 4 mango y 15 1 mango y 16 5 mango y 17 5 mango y 18 3 mango y

 Cell Formula C2 =IF(COUNTIFS(\$B\$2:\$B\$18,B2,\$A\$2:\$A\$18,">=5")>0,"y","n")

4. ## 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. ## Re: Formula merging a countif with a second if statement

Originally Posted by jtakw
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.