Forum Discussion
Solved
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 ...
You can use SUMIFS and COUNTIFS.
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