getting total count from drop down data list in google sheet

Question

I have created a google sheet to enter data of a hospital. One of the column is 'GENDER' where I have created drop down using data validation -> list of items and the items are (M,F) and similarly I have created another drop down value for the column 'CASES' where values are (NORMAL CORONA,DISCHARGED,DIED,ISOLATION,VENTILATOR,COMA). Health workers will input data in this sheet. I have neglected other columns.

Now in another tab, I have to give the aggregate report of the hospital patient i.e., total count of male and female patients for different cases. The structure of the aggregate report will be:

        NORMAL CORONA   DISCHARGED  DIED    ISOLATION   VENTILATOR  COMA
MALE
FEMALE

How can I create a report in google sheet from the drop down data list which shows total number of M/F gender count for the different CASES.

Answer 1

=ArrayFormula(COUNTIF(J:J&K:K;A2:A3&B1:H1))

where J:J is your 'GENDER column'

K:K is your 'CASES column'

Answer 2

Try a query() with a pivot clause, like this:

=query( 
  data!A1:K, 
  "select C, count(C) 
   where F is not null 
   group by C 
   pivot F", 
  1 
)

This is an array formula that creates the whole result table in one go.

The formula assumes that the gender is in column C and the case in column F.

Also see generate report of total count from drop down data list in google sheet.