0

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 Facility Name and Month for a particular set of Procedure Classes for my Hospital's inter-facility transfer report. So something like this:
=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))

Now my issue is this formula counts ALL transfers from the hospital being referenced (the $A reference is the month, the B$ reference is the facility name). I have to exclude 3 Procedure Classes from the overall count. The classes are as follows:

Ablation/EPS, Advanced Heart Failure/Transplant, Caths/Cath Possible, CTS, General Medicine/Cardiac Eval, Hem/Onc, Maternal Fetal Medicine, Neurology, Neurosurgery, Obstetrics, OB/GYN, PCTA/PCI/Stent, Renal Transplant, Stroke, Surgery (General/Onc), Trauma, Vascular Surgery, "" (Blanks).

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!

Here's the function:

=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, ""))

Ulli Schmid
  • 1,167
  • 1
  • 8
  • 16
mdaube
  • 81
  • 2
  • 2
  • 11
  • Wow, what a monster. I'm guessing I could do that pretty quickly with an **array formula** if you provided an anonymized (shortened) version of *Source Data* and a mockup of how your *Ideal Result* would look like. – Ulli Schmid Aug 10 '16 at 20:08

1 Answers1

1

Couldn't you just do the total minus those 3? Also, try using =IFERROR(1/(1/[Formula]),"") so you don't have to write things twice. So something like

=IFERROR(1/(1/(COUNTIFS([area1],[cond1],[area2],[cond2])-COUNTIFS([area1],[cond1],[area2],[cond2],[cond3],[excl1]) -COUNTIFS([area1],[cond1],[area2],[cond2],[area3],excl2) -COUNTIFS([area1],[cond1],[area2],[cond2],[area3],excl3)),"")

Or if you make a list of the exclusions, like exclusions, and array enter (CTRL-SHIFT-ENTER)

=IFERROR(1/(1/(COUNTIFS([area1],[cond1],[area2],[cond2])-SUM(COUNTIFS([area1],[cond1],[area2],[cond2],[area3],exclusions))),"")
dashnick
  • 2,020
  • 19
  • 34
  • wow... you're so right. I'm gonna try this, give me a second and I'll let you know if it works! I didn't even think of starting from the end product and subtracting! xD – mdaube Aug 10 '16 at 20:22
  • YEP! See, this is why I love StackOverflow. I spent the better part of an hour trying to troubleshoot that monstrosity and within 5 minutes someone comes along, gives me a reality check, and reminds me that sometimes the simplest solution is the least obvious! :) This totally worked and didn't take any time at all!! Thanks dashnick! – mdaube Aug 10 '16 at 20:30