Forum Discussion
extracting data using multiple conditions
Hi,
I've got a dataset which i want to summarise. in column 2 in the summary table below, i want to bring back a count of total number of instances were the following conditions are fulfilled in the dataset.
1. the "hospital" column is only NPH
2. and the "destination" column is only "home",
3. and the "service" column is "reablement"
4. and the week ending column date matches the date in the summary.
in this instant the summary table for 07/01/23, column 2 would only be 1, but is there a formula that would bring this back using the conditions above.
what adjustment would i need to make to the formula so that in column 3 of the table, conditions 1,2,4 still apply but condition 3 is adapted so that it brings back anything other than "reablement".
In column 4 of the summary, how would i bring back the sum of total number of hrs in the "weekly hrs column" in the dataset meeting all the 4 conditions above?
SUMMARY
| Week ending | Number per week Home care reablement | Number per week Home care other | total hours per week homecare reablement | total hours per week homecare other |
| 07/01/2023 | ||||
| 14/01/2023 | ||||
| 21/01/2023 | ||||
| 28/01/2023 |
DATASET
| Hospital | Hospital Discharge Date | Destination on discharge | Service | Weekly cost | Wkly Hours | Month | week ending |
| Woodlands Hall | 06/01/2023 | Home | Interim | £212.98 | 12.83 | January | 07/01/2023 |
| NPH | 07/01/2023 | Home | Reablement | £189.00 | 10.50 | January | 07/01/2023 |
| NPH | 09/01/2023 | Home | Reablement | £419.94 | 23.33 | January | 14/01/2023 |
| NPH | 09/01/2023 | Nursing | Knights Court | £1,600.00 | January | 14/01/2023 | |
| NPH | 09/01/2023 | Residential | Kent House | £1,100.00 | January | 14/01/2023 | |
| Charing Cross | 09/01/2023 | Home | Reablement | £588.06 | 32.67 | January | 14/01/2023 |
| NPH | 09/01/2023 | Home | Interim | £212.98 | 12.83 | January | 14/01/2023 |
| NPH | 09/01/2023 | Nursing | Rowan weald | £1,250.00 | January | 14/01/2023 | |
| NPH | 10/01/2023 | Home | Interim | £309.92 | 18.67 | January | 14/01/2023 |
| NPH | 13/01/2023 | Home | Interim | £44.32 | 2.67 | January | 21/01/2023 |
| NPH | 15/01/2023 | Home | Interim | £77.52 | 4.67 | January | 21/01/2023 |
| NPH | 22/01/2023 | Home | Reablement | £273.06 | 15.17 | January | 28/01/2023 |
| Ealing | 23/01/2023 | Residential | Sancroft | £460.10 | January | 28/01/2023 | |
| NPH | 23/01/2023 | Residential | St Joseph | £457.28 | January | 28/01/2023 |
You can use SUMIFS and COUNTIFS.
4 Replies
Let's say your data are on Sheet1.
In B2 on the summary sheet:
=COUNTIFS(Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"Reablement",Sheet1!$H$2:$H$15,$A2)
In C2:
=COUNTIFS(Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"<>Reablement",Sheet1!$H$2:$H$15,$A2)
In D2:
=SUMIFS(Sheet1!$F$2:$F$15,Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"Reablement",Sheet1!$H$2:$H$15,$A2)
In E2:
=SUMIFS(Sheet1!$F$2:$F$15,Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"<>Reablement",Sheet1!$H$2:$H$15,$A2)
Fill down.
- Thank you, this works perfectly
You can use SUMIFS and COUNTIFS.
- thanks, this works perfectly
