Forum Discussion
Randy Brouwer
Nov 03, 2017Copper Contributor
Count.ifs to count number of 'Beds taken'
In order to count 'the numer of beds taken per day', based on arrival date and discharge date of the patient, I constructed this Count.ifs formula:
=COUNT.IFS($A$2:$A$1306;"<=1-1-2016";$E$2:$E$1306;">=1-1-2016")
Column A = admission date
Column E = discharge date
So far, so good, this works.
The problem is I have to analyse this for a whole year and I can't 'drag down' the formula above, since the date won't adjust automatically. So I created a column with the date in it and rewrote the formula to
=COUNT.IFS($A$2:$A$1306;"<=J2";$E$2:$E$1306;">=J2")
Column J = the date for number of beds taken
Then the result directly turns to "0" instead of the true values I had with the other formula.
Does anyone know how I can let the date 'autoadjust' when I drag down, or how I can correctly use a Cell value as a criteria in the second value?
Hi Randy,
"<=" & J2
and so on
2 Replies
Sort By
Hi Randy,
"<=" & J2
and so on
- Randy BrouwerCopper Contributor
Thank you very much! Made my day, problem solved!! :)