Count Ocurrences of text in Range with multiple conditions

ajhavery · Oct 13, 2017

I'm working on a data set as displayed below.

I need to Index the maintenance and breakdown schedule of few equipment. The screenshot below displays the data for 2 equipment (Column G) with their maintenance schedule.

Column A & B lists the Start date and time of maintenance/breakdown, Column C & D lists the completion date and time of Maintenance/Breakdown. I need to fill in the data in last 2 columns for Index of Maintenance & Breakdown for 2 equipment LPDT 65 and 66 (Column G).

The data in Column N and O are filled as per below logic. For each Equip # (Column G), If Column M is "PM" and Column E is "Not repeated", Increase Maintenance # by 1.
Similarly for Column O, For each Equip # (Column G), If Column M is "BD" and Column E is "Not repeated", Increase Breakdown # by 1.

Kindly help me how I can do this in excel without VBA code.

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:67px;" /><col style="width:51px;" /><col style="width:88px;" /><col style="width:53px;" /><col style="width:70px;" /><col style="width:89px;" /><col style="width:44px;" /><col style="width:106px;" /><col style="width:43px;" /><col style="width:31px;" /><col style="width:30px;" /><col style="width:62px;" /><col style="width:63px;" /><col style="width:80px;" /><col style="width:72px;" /></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><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Raised Date</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Time In</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Completion date</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Time Out</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Repeat</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Lapse Time (hrs)</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Equip #</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Eq Desc</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Make</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Type</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Shift</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Comp Code</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Maint Type</td><td style="font-weight:bold; font-size:9pt; text-align:left; ">Maintenance #</td><td style="font-weight:bold; font-size:9pt; text-align:left; ">Breakdown #</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:8pt; text-align:right; ">9-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:45 PM</td><td style="font-size:8pt; text-align:right; ">9-Apr-17</td><td style="font-size:8pt; text-align:right; ">9:30 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">0.75</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:8pt; text-align:right; ">11-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">11-Apr-17</td><td style="font-size:8pt; text-align:right; ">10:00 AM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">2.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">7060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:8pt; text-align:right; ">12-Apr-17</td><td style="font-size:8pt; text-align:right; ">12:00 AM</td><td style="font-size:8pt; text-align:right; ">12-Apr-17</td><td style="font-size:8pt; text-align:right; ">6:00 AM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">C</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:8pt; text-align:right; ">17-Apr-17</td><td style="font-size:8pt; text-align:right; ">10:00 AM</td><td style="font-size:8pt; text-align:right; ">17-Apr-17</td><td style="font-size:8pt; text-align:right; ">3:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">5.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">4250</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:00 PM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">4.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:8pt; text-align:right; ">21-Apr-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">21-Apr-17</td><td style="font-size:8pt; text-align:right; ">10:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:8pt; text-align:right; ">22-Apr-17</td><td style="font-size:8pt; text-align:right; ">11:30 AM</td><td style="font-size:8pt; text-align:right; ">22-Apr-17</td><td style="font-size:8pt; text-align:right; ">12:30 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">1.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:8pt; text-align:right; ">10-May-17</td><td style="font-size:8pt; text-align:right; ">1:00 PM</td><td style="font-size:8pt; text-align:right; ">10-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">3.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">4250</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:8pt; text-align:right; ">11-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">11-May-17</td><td style="font-size:8pt; text-align:right; ">2:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:8pt; text-align:right; ">12-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">12-May-17</td><td style="font-size:8pt; text-align:right; ">10:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">1450</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:8pt; text-align:right; ">14-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">14-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:8pt; text-align:right; ">14-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">12:00 AM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">12:00 AM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">C</td><td style="font-size:8pt; text-align:center; ">7400</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">10:30 AM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">2.50</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">7400</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">5:30 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">1.50</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">1400</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; text-align:left; ">5</td></tr></table> 

I asked a similar question at: https://www.mrexcel.com/forum/excel-questions/1026920-conditional-ranking-cell-values.html, Kindly ignore the previous post.

ajhavery · Oct 13, 2017

I'm currently using a workaround method for the same as shown below. This doesn't work as intended and gives wrong result.

Test 1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:67px;" /><col style="width:51px;" /><col style="width:88px;" /><col style="width:53px;" /><col style="width:70px;" /><col style="width:89px;" /><col style="width:44px;" /><col style="width:106px;" /><col style="width:43px;" /><col style="width:31px;" /><col style="width:30px;" /><col style="width:62px;" /><col style="width:63px;" /><col style="width:80px;" /><col style="width:72px;" /></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><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Raised Date</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Time In</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Completion date</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Time Out</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Repeat</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Lapse Time (hrs)</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Equip #</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Eq Desc</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Make</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Type</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Shift</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Comp Code</td><td style="font-weight:bold; font-size:9pt; text-align:center; ">Maint Type</td><td style="font-weight:bold; font-size:9pt; text-align:left; ">Maintenance #</td><td style="font-weight:bold; font-size:9pt; text-align:left; ">Breakdown #</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:8pt; text-align:right; ">9-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:45 PM</td><td style="font-size:8pt; text-align:right; ">9-Apr-17</td><td style="font-size:8pt; text-align:right; ">9:30 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">0.75</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; text-align:left; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:8pt; text-align:right; ">11-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">11-Apr-17</td><td style="font-size:8pt; text-align:right; ">10:00 AM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">2.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">7060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; text-align:left; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:8pt; text-align:right; ">12-Apr-17</td><td style="font-size:8pt; text-align:right; ">12:00 AM</td><td style="font-size:8pt; text-align:right; ">12-Apr-17</td><td style="font-size:8pt; text-align:right; ">6:00 AM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">C</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:8pt; text-align:right; ">17-Apr-17</td><td style="font-size:8pt; text-align:right; ">10:00 AM</td><td style="font-size:8pt; text-align:right; ">17-Apr-17</td><td style="font-size:8pt; text-align:right; ">3:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">5.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">4250</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">18-Apr-17</td><td style="font-size:8pt; text-align:right; ">8:00 PM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">4.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:8pt; text-align:right; ">21-Apr-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">21-Apr-17</td><td style="font-size:8pt; text-align:right; ">10:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">3</td><td style="font-size:8pt; text-align:left; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:8pt; text-align:right; ">22-Apr-17</td><td style="font-size:8pt; text-align:right; ">11:30 AM</td><td style="font-size:8pt; text-align:right; ">22-Apr-17</td><td style="font-size:8pt; text-align:right; ">12:30 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">1.00</td><td style="background-color:#ccffcc; font-size:8pt; text-align:center; ">LPDT 65</td><td style="font-size:8pt; ">TH330 S/N T320D416</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">3</td><td style="font-size:8pt; text-align:left; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:8pt; text-align:right; ">10-May-17</td><td style="font-size:8pt; text-align:right; ">1:00 PM</td><td style="font-size:8pt; text-align:right; ">10-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">3.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">4250</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; text-align:left; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:8pt; text-align:right; ">11-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">11-May-17</td><td style="font-size:8pt; text-align:right; ">2:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">5060</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; text-align:left; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:8pt; text-align:right; ">12-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">12-May-17</td><td style="font-size:8pt; text-align:right; ">10:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">6.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">1450</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">1</td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:8pt; text-align:right; ">14-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">14-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:8pt; text-align:right; ">14-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">12:00 AM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">7500</td><td style="font-size:8pt; text-align:center; ">PM</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">12:00 AM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">8.00</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">C</td><td style="font-size:8pt; text-align:center; ">7400</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">8:00 AM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">10:30 AM</td><td style="background-color:#fce4d6; font-size:8pt; ">Repeated</td><td style="font-size:8pt; text-align:center; ">2.50</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">A</td><td style="font-size:8pt; text-align:center; ">7400</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">4:00 PM</td><td style="font-size:8pt; text-align:right; ">15-May-17</td><td style="font-size:8pt; text-align:right; ">5:30 PM</td><td style="font-size:8pt; ">Not repeated</td><td style="font-size:8pt; text-align:center; ">1.50</td><td style="background-color:#ffffcc; font-size:8pt; text-align:center; ">LPDT 66</td><td style="font-size:8pt; ">TH330 S/N T320D417</td><td style="font-size:8pt; ">Sandvik</td><td style="font-size:8pt; ">50 T</td><td style="font-size:8pt; text-align:center; ">B</td><td style="font-size:8pt; text-align:center; ">1400</td><td style="font-size:8pt; text-align:center; ">BD</td><td style="font-size:8pt; text-align:left; ">2</td><td style="font-size:8pt; text-align:left; ">4</td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</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 >E2</td><td >=IF(AND(D1=B2, C1=A2),"Repeated","Not repeated")</td></tr><tr><td >N2</td><td >=IF(G2<>G1, 1, IF(AND(M2="PM", E2="Not repeated"), N1+1,N1))</td></tr><tr><td >O2</td><td >=IF(G2<>G1, 1, IF(AND(M2="BD", E2="Not repeated"), O1+1,O1))</td></tr><tr><td >E3</td><td >=IF(AND(D2=B3, C2=A3),"Repeated","Not repeated")</td></tr><tr><td >N3</td><td >=IF(G3<>G2, 1, IF(AND(M3="PM", E3="Not repeated"), N2+1,N2))</td></tr><tr><td >O3</td><td >=IF(G3<>G2, 1, IF(AND(M3="BD", E3="Not repeated"), O2+1,O2))</td></tr><tr><td >E4</td><td >=IF(AND(D3=B4, C3=A4),"Repeated","Not repeated")</td></tr><tr><td >N4</td><td >=IF(G4<>G3, 1, IF(AND(M4="PM", E4="Not repeated"), N3+1,N3))</td></tr><tr><td >O4</td><td >=IF(G4<>G3, 1, IF(AND(M4="BD", E4="Not repeated"), O3+1,O3))</td></tr><tr><td >E5</td><td >=IF(AND(D4=B5, C4=A5),"Repeated","Not repeated")</td></tr><tr><td >N5</td><td >=IF(G5<>G4, 1, IF(AND(M5="PM", E5="Not repeated"), N4+1,N4))</td></tr><tr><td >O5</td><td >=IF(G5<>G4, 1, IF(AND(M5="BD", E5="Not repeated"), O4+1,O4))</td></tr><tr><td >E6</td><td >=IF(AND(D5=B6, C5=A6),"Repeated","Not repeated")</td></tr><tr><td >N6</td><td >=IF(G6<>G5, 1, IF(AND(M6="PM", E6="Not repeated"), N5+1,N5))</td></tr><tr><td >O6</td><td >=IF(G6<>G5, 1, IF(AND(M6="BD", E6="Not repeated"), O5+1,O5))</td></tr><tr><td >E7</td><td >=IF(AND(D6=B7, C6=A7),"Repeated","Not repeated")</td></tr><tr><td >N7</td><td >=IF(G7<>G6, 1, IF(AND(M7="PM", E7="Not repeated"), N6+1,N6))</td></tr><tr><td >O7</td><td >=IF(G7<>G6, 1, IF(AND(M7="BD", E7="Not repeated"), O6+1,O6))</td></tr><tr><td >E8</td><td >=IF(AND(D7=B8, C7=A8),"Repeated","Not repeated")</td></tr><tr><td >N8</td><td >=IF(G8<>G7, 1, IF(AND(M8="PM", E8="Not repeated"), N7+1,N7))</td></tr><tr><td >O8</td><td >=IF(G8<>G7, 1, IF(AND(M8="BD", E8="Not repeated"), O7+1,O7))</td></tr><tr><td >E9</td><td >=IF(AND(D8=B9, C8=A9),"Repeated","Not repeated")</td></tr><tr><td >N9</td><td >=IF(G9<>G8, 1, IF(AND(M9="PM", E9="Not repeated"), N8+1,N8))</td></tr><tr><td >O9</td><td >=IF(G9<>G8, 1, IF(AND(M9="BD", E9="Not repeated"), O8+1,O8))</td></tr><tr><td >E10</td><td >=IF(AND(D9=B10, C9=A10),"Repeated","Not repeated")</td></tr><tr><td >N10</td><td >=IF(G10<>G9, 1, IF(AND(M10="PM", E10="Not repeated"), N9+1,N9))</td></tr><tr><td >O10</td><td >=IF(G10<>G9, 1, IF(AND(M10="BD", E10="Not repeated"), O9+1,O9))</td></tr><tr><td >E11</td><td >=IF(AND(D10=B11, C10=A11),"Repeated","Not repeated")</td></tr><tr><td >N11</td><td >=IF(G11<>G10, 1, IF(AND(M11="PM", E11="Not repeated"), N10+1,N10))</td></tr><tr><td >O11</td><td >=IF(G11<>G10, 1, IF(AND(M11="BD", E11="Not repeated"), O10+1,O10))</td></tr><tr><td >E12</td><td >=IF(AND(D11=B12, C11=A12),"Repeated","Not repeated")</td></tr><tr><td >N12</td><td >=IF(G12<>G11, 1, IF(AND(M12="PM", E12="Not repeated"), N11+1,N11))</td></tr><tr><td >O12</td><td >=IF(G12<>G11, 1, IF(AND(M12="BD", E12="Not repeated"), O11+1,O11))</td></tr><tr><td >E13</td><td >=IF(AND(D12=B13, C12=A13),"Repeated","Not repeated")</td></tr><tr><td >N13</td><td >=IF(G13<>G12, 1, IF(AND(M13="PM", E13="Not repeated"), N12+1,N12))</td></tr><tr><td >O13</td><td >=IF(G13<>G12, 1, IF(AND(M13="BD", E13="Not repeated"), O12+1,O12))</td></tr><tr><td >E14</td><td >=IF(AND(D13=B14, C13=A14),"Repeated","Not repeated")</td></tr><tr><td >N14</td><td >=IF(G14<>G13, 1, IF(AND(M14="PM", E14="Not repeated"), N13+1,N13))</td></tr><tr><td >O14</td><td >=IF(G14<>G13, 1, IF(AND(M14="BD", E14="Not repeated"), O13+1,O13))</td></tr><tr><td >E15</td><td >=IF(AND(D14=B15, C14=A15),"Repeated","Not repeated")</td></tr><tr><td >N15</td><td >=IF(G15<>G14, 1, IF(AND(M15="PM", E15="Not repeated"), N14+1,N14))</td></tr><tr><td >O15</td><td >=IF(G15<>G14, 1, IF(AND(M15="BD", E15="Not repeated"), O14+1,O14))</td></tr><tr><td >E16</td><td >=IF(AND(D15=B16, C15=A16),"Repeated","Not repeated")</td></tr><tr><td >N16</td><td >=IF(G16<>G15, 1, IF(AND(M16="PM", E16="Not repeated"), N15+1,N15))</td></tr><tr><td >O16</td><td >=IF(G16<>G15, 1, IF(AND(M16="BD", E16="Not repeated"), O15+1,O15))</td></tr><tr><td >E17</td><td >=IF(AND(D16=B17, C16=A17),"Repeated","Not repeated")</td></tr><tr><td >N17</td><td >=IF(G17<>G16, 1, IF(AND(M17="PM", E17="Not repeated"), N16+1,N16))</td></tr><tr><td >O17</td><td >=IF(G17<>G16, 1, IF(AND(M17="BD", E17="Not repeated"), O16+1,O16))</td></tr></table></td></tr></table>

alansidman · Oct 14, 2017

crosspost: https://www.ozgrid.com/forum/forum/...12-rank-occurrences-of-text-with-if-condition

Count Ocurrences of text in Range with multiple conditions

ajhavery

New Member

Excel Facts

ajhavery

New Member

alansidman

Well-known Member

Similar threads

Forum statistics

Share this page

Count Ocurrences of text in Range with multiple conditions

ajhavery

New Member

Excel Facts

ajhavery

New Member

alansidman

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock