google sheet count groups reported month wise

Question

I have a data table that contains the data of reporting groups, date wise for 3 months for

example enter image description here

I want to count the simplify the data in the below format

I need a google sheet formula to calculate this.

enter image description here

Answer 1

Try below formula-

=SUMPRODUCT((('Reporting Groups'!$C$2:$CO$48="Y")+('Reporting Groups'!$C$2:$CO$48="Nil"))*('Reporting Groups'!$B$2:$B$48=$B2)*('Reporting Groups'!$C$1:$CO$1=C$1))