CODEWITHSUNDEEP
excelarray-formulas
Jack Deeth
Jack Deeth

Reputation: 3357

Using array formula for table-to-table analysis

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.

Upvotes: 2

Views: 539

Answers (2)

locofrog
locofrog

Reputation: 1

You can use this array formula:

={COUNTIFS(INDEX(B2:D5,,COLUMN()-1),">="&A8:A10,INDEX(B2:D5,,COLUMN()-1),"<"&A9:A11)}

p.s. table headers in your example have to be transposed, i.e., 

+----+------------+------------+------------+------------+
|    |     A      |     B      |     C      |     D      |
+----+------------+------------+------------+------------+
|  1 |            | patent 1   | patient 2  | patient 3  |
+----+------------+------------+------------+------------+
|  2 | visit 1    |            |            |            |
+----+------------+------------+------------+------------+            
|  3 | visit 2    |

Upvotes: 0

JosieP
JosieP

Reputation: 3410

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)

Upvotes: 1

Related Questions