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?
