COUNTIFS not working for 2 values, but PIVOT is working fine

Hi All,
COUNTIFS not working for 2 values (7 Courses & 8 Dept.). Need to count each employee assigned to what course & from which dept. The marking is by dates.
Nevertheless, PIVOT table & chart is working fine. I am trying to avoid PIVOT table if possible


Attached the sample file.

Thank-You very much!

Hi shukla.ankur281190,

this perfectly works:

=sumproduct(($d$4:$d$96=d$98)*($e$3:$k$3=$c99)*('2020'!$e$4:$k$96<>""))

this partly works: (drag to the followings rows & columns but the value same as the top

=COUNTIFS(INDEX($E$4:$K$96,,MATCH($C99,$E$3:$K$3,0)),"<>""",$D$4:$D$96,D$98)

you think could fix for COUNTIFS?

Thank-You very much!

Hi shukla.ankur281190,

I am looking at your COUNTIFS, still cant detect why the values cannot be copied to the bottom.
I will just leave this thread open & added reputation to you..

Thank-You very much