Help with setting up spreadsheet and charts please

[matthewluck1]

Hi, I would be extremely grateful for some help with my spreadsheet.

I have the following data which I need to use to produce charts for a
hospital report fairly urgently.

The rows are the names of patients. This could be up to 200 for each month,
but this varies from month to month. I will have each month on a different
worksheet.

Column F - Ward
Column G - Hospital
Column K - I have a number 1 in the row if the patient meets the criteria

I need to produce a chart(s) to show and compare the following:

How many patients from each ward met the criteria (as indicated by 1 in
column K) and how many did not.

How many patients from each hospital met the criteria and how many did not.

I would really like some help!!!

 

[PBezucha]

Matthew,
Classical homework, isn’t it? It’s an example for using Excel function SUMIF
(find out the Help for this function). Make somewhere the column list of you
Wards. Next to the first Ward insert the function SUMIF. As its Range
argument select the range of your Wards from the patients list, as Criteria
select the link to the cell with Ward name, as Sum all of yours 1’s and
nothings or zeros. The ranges should be anchored properly ($’s). Copy the
SUMIF down for all Ward names. You will obtain the correct count in each
category. For graphical representation create bar chart.
For negative counting you have to know the totals of patients in Wards. You
obtain them by means of COUNTIF function. So, for each Ward from your list
insert this function to the next column with quite the same range and
criteria arguments as with SUMIF. The count of the not-meetings is mere a
difference.
Do the same with Hospitals.

 

[matthewluck1]

Thanks, I have got as far as counting the number of 1's in the patient list
using the formula: =SUMIF($F1:F89,L1:L26,J1:J89)

I am stuck using the COUNTIF function to count which cells are have 0's. I
have my summary of all wards mentioned in column L (L1:L26). My 0's and 1's
are found in column J (J1:J89). The ward on the main part of the sheet is on
each row in column G (G1:G89).

I would like the spreadsheet to look at the ward name in my summary in
column L, then if this matches in column G and there is an 0 in column J
alongside it, then this should be counted to add up to a cumulative total of
0's per ward.

Sorry for my lack of understanding I've not used excel for ages!!!

 

[PBezucha]

Put

=SUMIF($G$1:$G$100;L1;$J1:$J100) into M-column

=COUNTIF($G1:$G100;L1)-M1 into N-column.

I was overly didactic, take my apologies. Countif has also by one argument
fewer, I failed to mention, and that was what may have confused you.

Regards

 

[matthewluck1]

Thank you so much for your help. It is working fine now.

 

[matthewluck1]

Thanks again. Sorry to be a pain, but I have one more question / formula to
work out and have tried to change the ones already used but to no avail.

In cell O3 and O4 I have the total number of patients (one cell per
hospital). In cell P3 and P4, I need a formula to display the number of
patients who me the criteria.

Back to the main part of my spreadsheet, I have the name of the hospital in
the F row from F3 to F91. In the same rows but in the G column, I have a 1 if
the criteria was met and 0 if it was not.

I need some sort of formula to look at the number of patients who met the
criteria by hospital name.

Matthew

 

[PBezucha]

Matthew,
Just by the strike of fortune I looked, after ages, into my post, and found
that after the acknowledgment an additional post came from you.
I had taken it that the wards and hospitals were classifying variables of
the same position towards meeting criteria. I don’t see any difference
whether there are wards in the column F or G or hospitals in the column F or
G, suppose there is still the same column (G or K??) with 0 or 1’s. Exactly
the same function COUNTIF and SUMIF should have been applied, as I had
advised in my first reply.
Where have you lost your head since?
Regards
Petr