COUNTIF formula with condition

Bolter LAC

Board Regular
Joined
Aug 13, 2008
Messages
140
Hello once again,

I need a countif formula (or similar) with a condition that it does not count a particular cell that is adjacent(same row) as an event known as "OPEN". The formula will count all the 'CR's in the row and ignore the cell adjacent to REGION even though there will be a CR in the cell. Formula will go in E14 and based on the below scenario the result would =1. CE12 being ignored.

Suggestions

Thanks Bolter

16B9

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 35px"><COL style="WIDTH: 117px"><COL style="WIDTH: 64px"><COL style="WIDTH: 35px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #a5855b">Total PB's</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #a5855b; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #538ed5"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #538ed5"></TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">EVENT</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">70M</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: #ff0000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">PB</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: #ff0000; FONT-FAMILY: Arial; TEXT-ALIGN: center">8.37</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: #99cc00; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">CLUB REC</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: #99cc00; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">8.38</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8; TEXT-ALIGN: center">DATE</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #d8d8d8"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">10/02/2009</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">8.50</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-SIZE: 14pt; COLOR: #008000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">21/02/2009</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">8.40</TD><TD style="FONT-SIZE: 14pt; COLOR: #ff0000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">PB</TD><TD style="FONT-SIZE: 14pt; COLOR: #008000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">OPEN</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">8.38</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-SIZE: 14pt; COLOR: #008000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">CR</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">29/02/2009</TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">8.37</TD><TD style="FONT-SIZE: 14pt; COLOR: #ff0000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">PB</TD><TD style="FONT-SIZE: 14pt; COLOR: #008000; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">CR</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BACKGROUND-COLOR: #33cccc"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-SIZE: 14pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ece9d8"></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C5</TD><TD>=SUM(D38,G38,J38,M38,P38,S38,V38,Y38,AB38,AE38,AH38,AK38,AN38,AQ38,AT38,AW38,AZ38)</TD></TR><TR><TD>C7</TD><TD>{=MIN(IF(1-ISNUMBER(MATCH(B10:B28,{"OPEN","REGION"},0)),IF(ISNUMBER(C10:C28),C10:C28)))}</TD></TR><TR><TD>C8</TD><TD>='Club Record'!D35</TD></TR><TR><TD>B10</TD><TD>='Program Dates'!B4</TD></TR><TR><TD>E10</TD><TD>=IF(ISBLANK(C10)," ",IF(ISTEXT(C10)," ",IF(C10<=MIN(C$8:C8),"CR"," ")))</TD></TR><TR><TD>B11</TD><TD>='Program Dates'!B5</TD></TR><TR><TD>D11</TD><TD>{=IF(OR($B11={"REGION","OPEN"},$C11=0),"",REPT("PB",$C11<=MIN(IF(($B$10:$B10<>{"REGION","OPEN"})*(ISNUMBER($C$10:$C10)),$C$10:$C10))))}</TD></TR><TR><TD>E11</TD><TD>=IF(ISBLANK(C11)," ",IF(ISTEXT(C11)," ",IF(C11<=MIN(C$8:C10),"CR"," ")))</TD></TR><TR><TD>B12</TD><TD>='Program Dates'!B6</TD></TR><TR><TD>D12</TD><TD>{=IF(OR($B12={"REGION","OPEN"},$C12=0),"",REPT("PB",$C12<=MIN(IF(($B$10:$B11<>{"REGION","OPEN"})*(ISNUMBER($C$10:$C11)),$C$10:$C11))))}</TD></TR><TR><TD>E12</TD><TD>=IF(ISBLANK(C12)," ",IF(ISTEXT(C12)," ",IF(C12<=MIN(C$8:C11),"CR"," ")))</TD></TR><TR><TD>B13</TD><TD>='Program Dates'!B7</TD></TR><TR><TD>D13</TD><TD>{=IF(OR($B13={"REGION","OPEN"},$C13=0),"",REPT("PB",$C13<=MIN(IF(($B$10:$B12<>{"REGION","OPEN"})*(ISNUMBER($C$10:$C12)),$C$10:$C12))))}</TD></TR><TR><TD>E13</TD><TD>=IF(ISBLANK(C13)," ",IF(ISTEXT(C13)," ",IF(C13<=MIN(C$8:C12),"CR"," ")))</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
xld,

I am assuming that C was supposed to be B in your suggestion. Tried it and it counts CR ignoring cell adjacent to REGION however it does not pick up on new a CR if the time is better than a previous cell. If I added a row to the same sample worksheet and then entered a time that was a CR (club record) the formula does not count this.

Thanks Bolter
 
Upvote 0
xld,

I am assuming that C was supposed to be B in your suggestion. Tried it and it counts CR ignoring cell adjacent to REGION however it does not pick up on new a CR if the time is better than a previous cell. If I added a row to the same sample worksheet and then entered a time that was a CR (club record) the formula does not count this.

Thanks Bolter
Perhaps a more comprehensive example would help? You have referred a couple of times to 'REGION', but there is no such thing in your sample data.
 
Upvote 0
Peter_Sss,

My mistake I have said REGION when it was supposed to be OPEN. I have pasted in my previous question and one answer with the correct details.

"I need a countif formula (or similar) with a condition that it does not count a particular cell that is adjacent(same row) as an event known as "OPEN". The formula will count all the 'CR's in the row and ignore the cell adjacent to "OPEN" even though there will be a CR in the cell. Formula will go in E14 and based on the below scenario the result would =1. CE12 being ignored."

"I am assuming that C was supposed to be B in your suggestion. Tried it and it counts CR ignoring cell adjacent to OPEN however it does not pick up on new a CR if the time is better than a previous cell. If I added a row to the same sample worksheet and then entered a time that was a CR (club record) the formula does not count this."

I hope that makes more sense.

Bolter
 
Upvote 0
Then maybe this slight modification to xld's suggestion?
=SUMPRODUCT(--(B5:B13<>"OPEN"),--(E5:E13="CR"))
 
Upvote 0
Peter_Sss,

Thats the one. Thank you very much. And to you xld for getting the ball rolling.

Grateful

Bolter
 
Upvote 0
Peter_Sss,

Thats the one. Thank you very much. And to you xld for getting the ball rolling.

Grateful

Bolter
Glad to help. :)

Just one other thing for the future that will make your questions clearer ...

You said you wanted to count all the 'CR's in the row. Remember that rows run horizontally and columns run vertically, so you actually wanted to count the 'CR's in the column.
 
Upvote 0

Forum statistics

Threads
1,226,285
Messages
6,190,055
Members
453,591
Latest member
plengeb

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