This represents what I'm trying to do:
+----+--------------------+------------+------------+------------+
| | A | B | C | D |
+----+--------------------+------------+------------+------------+
| 1 | | visit 1 | visit 2 | visit 3 |
| 2 | patient 1 | 11/01/2011 | 25/01/2011 | 17/02/2011 |
| 3 | patient 2 | 17/01/2011 | 1/04/2011 | 2/07/2011 |
| 4 | patient 3 | 18/04/2011 | 19/04/2011 | 20/06/2011 |
| 5 | patient 4 | 18/09/2011 | 26/09/2011 | 29/09/2011 |
| 6 | | | | |
| 7 | visits per quarter | | | |
| 8 | 1/01/2011 | 2 | 1 | 1 |
| 9 | 1/04/2011 | 1 | 2 | 1 |
| 10 | 1/07/2011 | 1 | 1 | 2 |
| 11 | 1/10/2011 | | | |
+----+--------------------+------------+------------+------------+
I'm calculating the visits per quarter (B8:D10) from the visit history (B2:D5).
The data in B2:D5 is from another sheet. I want to show how many of each type of visit occur/occurred in each quarter.
This formula works, as an ordinary (not array) formula, written in B8 then copied into B8:D10:
=SumProduct((B$2:B$5 >= $A8) * (B$2:B$5 < $A9))
How could I replace this with an array formula for the B8:D10 range here?
Closest I've got is this:
{=SumProduct((B2:D5 >= A8:A10) * (B2:D5 < A9:A11) * (COLUMN(B2:D5) = COLUMN(B8:D10)))}
which doesn't work.
if you can enter 1/1/2012 into A12 and you have xl2007 or later
=COUNTIFS(INDEX(B2:D5,,COLUMN(B1:D1)-1),">="&A8:A11,INDEX(B2:D5,,COLUMN(B1:D1)-1),"<"&A9:A12)