Problems with a countifs formula

[mark hansen]

I've been fighting with this formula for two days now and I'm starting to give up. I thought it would pretty basic.

I have a table that I need a formula to look at and produce the number of FALSE entries in a group of columns, that are attributed to a doc on a certain appointment date. When I put the criteria together, it gives me [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] , but when I try each of criteria separate, it provides the correct number... I'm trying to produce a grid of numbers with the dates on top, and doctor's names down the side.

Row 9 are dates, column AK are the doctor's names

The formula I want to work is:
=COUNTIFS(Table1[PAU Appt Date],AM$9,Table1[Staff Surgeon],$AK11,Table1[[H&P Done]:[Pre-Op teaching Done]],FALSE) this produces a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL]

All of these work...
=COUNTIFS(Table1[[H&P Done]:[Pre-Op teaching Done]],FALSE)
=COUNTIFS(Table1[PAU Appt Date],AM$9,Table1[Staff Surgeon],$AK12)
=COUNTIFS(Table1[PAU Appt Date],AM$9)

It seems like something may be wrong with the Table1[[H&P Done]:[Pre-Op teaching Done]],FALSE part. It works by itself, but not in combination with the other criteria.

I'm a little new at table notation, maybe I'm getting that wrong.

I even tool a wild stab at making it all ranges line =COUNTIFS(Table1[[PAU Appt Date]:[PAU Appt Date]],AM$9,Table1[[Staff Surgeon]:[Staff Surgeon]],$AK11,Table1[[H&P Done]:[Pre-Op teaching Done]],FALSE) But that different work.

Any ideas what I'm doing wrong?

 

[Eric W]

Your problem is that with a COUNTIFS, all of the ranges must be the same size. The one you want to work has 2 ranges that are 1 column wide, and 1 range that is 2 (or more) columns wide. You could just split it up, like this:

=COUNTIFS(Table1[PAU Appt Date],AM$9,Table1[Staff Surgeon],$AK11,Table1[H&P Done],FALSE)+COUNTIFS(Table1[PAU Appt Date],AM$9,Table1[Staff Surgeon],$AK11,Table1[Pre-Op teaching Done],FALSE)

or you could use SUMPRODUCT if you have more columns:

=SUMPRODUCT(EXACT(Table1[[H&P Done]:[Pre-Op teaching Done]],FALSE)*(Table1[PAU Appt Date]=AM$9)*(Table1[Staff Surgeon]=$AK11))

 

[mark hansen]

Thanks Eric! I didn’t think about approaching it that way.

 

[Eric W]

Glad to help!

 

[mark hansen]

thanks again Eric, I put this into practice at work today and and it helped a lot... Thanks again.