Countif based on range in other column

B

Besh

New Member
Joined
Jun 16, 2011
Messages
31
I'm writing an electronic survey on excel as per my employer's request. If the respondent clicks "fail", I don't want to count any of the "pass" responses for a particular section. I'm currently counting the repsonses as so:

=COUNTIF(E33:E42,"x")

That section of the survey looks like this:

<TABLE style="WIDTH: 509pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=677><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 221pt; mso-width-source: userset; mso-width-alt: 10752" width=294><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" id=td_post_53307 class=xl70 height=20 width=86></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=84></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=58></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 221pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=294></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 62pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=83>Pass</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; WIDTH: 54pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl85 width=72>Fail</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" dir=ltr class=xl72 height=20 colSpan=2>H. DIAGNOSTIC CATEGORY</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl73></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl75 height=20 colSpan=4>Should be indicated as: Osteoporosis, low (reduced) bone mass or normal</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" class=xl78 height=20 colSpan=2>Must be present and accurate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" dir=ltr class=xl72 height=20 colSpan=2>I. FRACTURE RISK CATEGORY</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl73></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(N/A for pediatric cases)</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" class=xl75 height=20 colSpan=3>Should be indicated as: High, low or moderate</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl78 height=20 colSpan=4>The source/reference to fracture risk must also be present (e.g. CAROC)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" dir=ltr class=xl72 height=20 colSpan=2>J. BMD RAW VALUES</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl76></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" class=xl78 height=20 colSpan=2>(gm/cm2 to 3 decimal places)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" class=xl73 height=20 colSpan=3>K. BMD T-SCORE (TO 1 DECIMAL PLACE)</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(Z-scores for pediatric cases)</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; mso-ignore: colspan" class=xl83 height=20 colSpan=2>Present to 1<SUP>st</SUP> decimal place</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69></TD></TR></TBODY></TABLE>

So I basically only want to Countif the "x" in the "Pass" column when there are no "Fails" at all.

Thanks, Alex
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
In excel 2007 you can use COUNTIFS

edit: just realized you said when there are NO fails at all...

so just use an IF statement in front of your regular count formula

=IF(COUNTIF(F33:F42,"x")>0,0,COUNTIF(E33:E42,"x"))
 
Last edited:
Upvote 0
Nice, thanks for that. I should've just posted awhile ago instead of trying to google my problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,583
Messages
6,179,671
Members
452,937
Latest member
Bhg1984

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