Formula merging a countif with a second if statement

B

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

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl65"]oranges[/TD]
[TD="class: xl72"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl67"]oranges[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl67"]oranges[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl67"]oranges[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]oranges[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl67"]oranges[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl68"]2[/TD]
[TD="class: xl69"]oranges[/TD]
[TD="class: xl74"]y[/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl65"]apples[/TD]
[TD="class: xl72"]n[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl67"]apples[/TD]
[TD="class: xl73"]n[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl67"]apples[/TD]
[TD="class: xl73"]n[/TD]
[/TR]
[TR]
[TD="class: xl68"]1[/TD]
[TD="class: xl69"]apples[/TD]
[TD="class: xl74"]n[/TD]
[/TR]
[TR]
[TD="class: xl70"]3[/TD]
[TD="class: xl71"]pears[/TD]
[TD="class: xl75"]n[/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl65"]mango[/TD]
[TD="class: xl72"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]mango[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl67"]mango[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl67"]mango[/TD]
[TD="class: xl73"]y[/TD]
[/TR]
[TR]
[TD="class: xl68"]3[/TD]
[TD="class: xl69"]mango[/TD]
[TD="class: xl74"]y[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
jtakw said:
Hi,


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(COUNTIFS(A$1:A$17,">4",B$1:B$17,B1),"y","n")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

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. :laugh:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
148
Peter_SSs
Peter_SSs
B
  • Solved
Conditional SUMPRODUCT Formula
Replies
4
Views
520
brandonej
B
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
101
hagia_sofia
hagia_sofia
M
  • Question Question
valid data input
Replies
5
Views
292
mwvirk
M
M
  • Question Question
Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner
Replies
7
Views
389
maeyks
M

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

Share this page

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