title: Excluding values based on countif functions and if statements
body: <p>I may have an easy question for some of you, but so far it has vexed me thoroughly.  I'm trying to pull data out of a worksheet based on <em>Facility Name</em> and <em>Month</em> for a particular set of <em>Procedure Classes</em> for my Hospital's inter-facility transfer report.  So something like this:<br>
  <code>=IF(COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2)=0, "", COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2))</code>  </p>
  
  <p>Now my issue is this formula counts <strong>ALL</strong> transfers from the hospital being referenced (the $A reference is the month, the B$ reference is the facility name).  I have to exclude 3 <em>Procedure Classes</em> from the overall count.  The classes are as follows:</p>
  
  <blockquote>
    <p>Ablation/EPS, Advanced Heart Failure/Transplant, Caths/Cath Possible,
    CTS, General Medicine/Cardiac Eval, Hem/Onc,
    <strong>Maternal Fetal Medicine</strong>, Neurology, Neurosurgery,
    <strong>Obstetrics</strong>,
    <strong>OB/GYN</strong>, PCTA/PCI/Stent, Renal Transplant, Stroke, Surgery (General/Onc), Trauma, Vascular Surgery, "" (Blanks).</p>
  </blockquote>
  
  <p>The values to be excluded are in bold.  I've come up with a wall of text and I can't figure out if there's a better way to do it.  Additionally, my wall isn't working, it's giving me a #NAME? error.  Any suggestions/direction/insight into where I screwed up would be a great help!</p>
  
  <p>Here's the function:</p>
  
  <p><code>=IF((COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Ablation/EPS") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Advanced Heart Failure/Transplant") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, “Caths/Cath Possible”) + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "CTS") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "General Medicine/Cardiac Eval") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Hem/Onc") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Neurology") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Neurosurgery") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "PCTA/PCI/Stent") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Renal Transplant") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Stroke") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Surgery (General/Onc)") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Trauma") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Vascular Surgery") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, ""))=0, “”, (COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Ablation/EPS") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Advanced Heart Failure/Transplant") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, “Caths/Cath Possible”) + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "CTS") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "General Medicine/Cardiac Eval") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Hem/Onc") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Neurology") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Neurosurgery") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "PCTA/PCI/Stent") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Renal Transplant") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Stroke") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Surgery (General/Onc)") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Trauma") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, "Vascular Surgery") + COUNTIFS('Source Data 2016'!$F:$F, B$1,'Source Data 2016'!$I:$I, $A2, 'Source Data 2016'!$H:$H, ""))</code></p>