Countifs not working

[Caitlin535]

I feel like a crazy person - I should be able to figure this out. I have a schedule of groups set up like the attached picture. I want the table at the bottom to tell me for each unit (D3B, D4B, etc.) how many groups are run by each discipline - Psychology, Psychiatry, Rehab, etc.
I'm trying to use Countifs to count up cells that a) have the correct unit in column B, and then have the correct discipline. I tried =COUNTIFS($B3:$B22,C26,$B$3:$L$22,D$25), but I know I'm doing something wrong because it gives the #value error. I woudl really appreciate if anyone has a suggestion - again, I apologize for having to ask something that seems so simple!

 

[SanjayGMusafir]

Try this -

=SUMPRODUCT(($C$3:$C$22=D$25)*($B3:$B22=$C26))

 

[portews]

The Countif's won't work on non-contiguous ranges. The workaround is to sum the rows.
So for the first one it would be:

=COUNTIF($C3:$L3,$D$25)+COUNTIF($C10:$L10,$D$25)+COUNTIF($C17:$L17,$D$25)

You can pull that straight down and fill the column since the list and cable are in the same order.