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-ignoreadding; 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-ignoreadding; 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!
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-ignoreadding; 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-ignoreadding; 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!