0

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.

1
0

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.

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.