Combination of two COUNTIF formulas not returning correct answers

Miss Sheldor

New Member
Joined
Sep 28, 2017
Messages
2
Hello, I'm having trouble trying to display results from a different sheet where my raw data is when I try to combine two COUNTIFs. The 1st one works and gives the correct value but when I try to add the 2nd I get an incorrect result.

I want a total of when the value in the 1st column is 'cervical' *and* the value in the 2nd column is 'Y' (not when it is 'cervical' and 'N' or 'U')

The 1st COUNTIF gives me the correct value in 'number' column, no problem

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}</style>=COUNTIF('Raw Data'!H2:H74,"CERVICAL")
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}</style>
The 2nd column I have tried to use to give the value in 'number comply with...' should give me 38, but is giving 33 - I can't work out why. I have checked leading/trailing spaces, all cells are formatted as general, none are blank and they are continuous.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}</style>
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}</style>
=COUNTIF('Raw Data'!H2:H74,"CERVICAL") - COUNTIF('Raw Data'!J2:J74,"N") - COUNTIF('Raw Data'!J2:J74,"U")

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 188"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Indication complies with PET guidelines[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {text-align:center;}.xl66 {color:#1F497D; font-weight:700; text-align:center; border-top:none; border-right:none; border-bottom:1.0pt solid #95B3D7 ; border-left:none; background:#C5D9F1; mso-pattern:black none;}--></style>[TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="class: xl66, width: 65"]NUMBER[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 196"]
<colgroup><col></colgroup><tbody>[TR]
[TD]NUMBER COMPLY WITH PET GUIDELINES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 188"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CERVICAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {text-align:center;}.xl66 {text-align:center; background:#CCC0DA; mso-pattern:black none;}--></style>[TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="class: xl66, width: 65"]40[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 196"]
<colgroup><col></colgroup><tbody>[TR]
[TD]33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any assistance!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

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