# Generate report of total count from drop down data list in Google Sheet

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. Sample columns in the sheet (first tab named '`data`') are:

``````S.NO.|| NAME || GENDER || ADDRESS || CONTACT_NUM  || CASES ||  ADMIT_DATE ||    OCCUPATION||RELATIVES_NAME||RELATIVES_CONTACT
``````

Now in another tab, I have to give the aggregate report of the hospital patient. 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`.

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
)
``````

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

you could simply use COUNTIFs to accomplish this.

You basically want to set up your formula to say "count this value if Column X = "Normal" AND Column Y = "Male"

It's hard to give an exact answer without seeing a sample sheet, but it would look something like this:

``````=countifs(A:A,"Male",B:B,"Normal")
``````

Then repeat the formula for all of your fields (male and female).

Depending on your structure, you could use the cell references to do it quicker, but that might be a little more complicated.