Unexpected results from a SUM COUNTIFS formula

[boznian]

Trying to count instances that meet the following criteria: =particular Month, =particular Doctor, =valid patient name. The column with patient name also contains entries such as "Off", "Out", "Break", etc. and these need to be NOT counted in this formula. (But need to stay in the source feed and be counted in a different formula.)
So the initial portion of the formula to count everything that matches the month and the doctor works fine:

=SUM(COUNTIFS(Feed!$P$2:$P$20000,J$1,Feed!$E$2:$E$20000,$A$1))

where A1 holds Dr. Name and J1 holds the current month to be evaluated. This formula results in 1,330, which is the correct number of entries in the raw feed. I need to eliminate those entries that contain things such as "Out", "Break", "Off" etc. which are in the SAME COLUMN as the valid patient name. So, I have been trying things such as:

=SUM(COUNTIFS(Feed!$P$2:$P$20000,J$1,Feed!$E$2:$E$20000,$A$1)-(COUNTIFS(Feed!I2:I20000,{"Out","Break","Off"})))

and the results vary from negative numbers to numbers higher than the original 1,330. I can't even find a pattern to the results I get, since for this particular month there are only 2 "Out" entries for this Dr. but the results go negative. Patient name column is formatted as 'General'. Patient names are actually first name, last initial. (And there isn't anybody named "Out") What am I doing wrong?

 

[Eric W]

Try:

=COUNTIFS(Feed!$P$2:$P$20000,J$1,Feed!$E$2:$E$20000,$A$1)-SUM(COUNTIFS(Feed!$P$2:$P$20000,J$1,Feed!$E$2:$E$20000,$A$1,Feed!I2:I20000,{"Out","Break","Off"}))

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(--(Feed!$P$2:$P$20000=J$1),--(Feed!$E$2:$E$20000=$A$1),--ISNA(MATCH(Feed!I2:I20000,{"Out","Break","Off"},0)))

M.

 

[boznian]

Great, THANKS! While both seemed to work perfect, I like the SUMPRODUCT version as it seems cleaner. HOWEVER, I forgot to mention that the column with names/out/break etc. also has other text occasionally after, necessitating an *. So, {"Out","Break","Off"})) has to become "Out*","Break*","Off*"})) which only seemed to work on the COUNTIFS version. Thanks to both, every post in this forum = LEARNING.

 

[Marcelo Branco]

Great, THANKS! While both seemed to work perfect, I like the SUMPRODUCT version as it seems cleaner. HOWEVER, I forgot to mention that the column with names/out/break etc. also has other text occasionally after, necessitating an *. So, {"Out","Break","Off"})) has to become "Out*","Break*","Off*"})) which only seemed to work on the COUNTIFS version. Thanks to both, every post in this forum = LEARNING.

I think is possible to adapt the SUMPRODUCT formula to handle this scenario. Something like
=SUMPRODUCT(--(Feed!$P$2:$P$20000=J$1),--(Feed!$E$2:$E$20000=$A$1),--(MMULT(--ISNUMBER(SEARCH({"Off","Out","Break"},Feed!I2:I20000)),{1;1;1})=0))

M.