# Unexpected results from a SUM COUNTIFS formula

#### boznian

##### Board Regular
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
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
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
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
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.

Replies
16
Views
370
Replies
0
Views
141
Replies
2
Views
180
Replies
3
Views
65
Replies
0
Views
119

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.

### Which adblocker are you using?

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

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