Unexpected results from a SUM COUNTIFS formula

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,804
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,519
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 25, 2003
Messages
167
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,519
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

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
Top