Formula merging a countif with a second if statement

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
190
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

ABC
4orangesy
3orangesy
2orangesy
2orangesy
1orangesy
5orangesy
2orangesy
4applesn
2applesn
4applesn
1applesn
3pearsn
4mangoy
1mangoy
5mangoy
5mangoy
3mangoy

<tbody>
</tbody>
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
If you have Excel 365 with the MAXIFS function, try the C2 formula. Otherwise, try the D2 formula.


Book1
ABCD
1ABCC
24orangesyy
33orangesyy
42orangesyy
52orangesyy
61orangesyy
75orangesyy
82orangesyy
94applesnn
102applesnn
114applesnn
121applesnn
133pearsnn
144mangoyy
151mangoyy
165mangoyy
175mangoyy
183mangoyy
Sheet2
Cell Formulas
RangeFormula
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")
 
Upvote 0
Other way:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">4</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">5</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2</td><td >oranges</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4</td><td >apples</td><td >n</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">2</td><td >apples</td><td >n</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">4</td><td >apples</td><td >n</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">1</td><td >apples</td><td >n</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">3</td><td >pears</td><td >n</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">4</td><td >mango</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">1</td><td >mango</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">5</td><td >mango</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">5</td><td >mango</td><td >y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">3</td><td >mango</td><td >y</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(COUNTIFS($B$2:$B$18,B2,$A$2:$A$18,">=5")>0,"y","n")</td></tr></table></td></tr></table>
 
Upvote 0
Hi,

Almost the same as Dante's:


Book1
ABC
14orangesy
23orangesy
32orangesy
42orangesy
51orangesy
65orangesy
72orangesy
84applesn
92applesn
104applesn
111applesn
123pearsn
134mangoy
141mangoy
155mangoy
165mangoy
173mangoy
Sheet698
Cell Formulas
RangeFormula
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.
 
Upvote 0
Hi,


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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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. :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top