Hello! Long time listener, first time caller.
I have a spreadsheet with rows for observations of individuals. The columns B-D indicate the dates of interest. Each date range in these columns is unique to the individual. The columns (G-L) are calendar dates -- there is a column for every day for 6 months. Individuals have an observation some days, and blanks other days. I want to know the number of observations in a given, unique date range for each individual.
I need help creating a formula that counts the number of values from Date X to Date Y (column E) and from Date X to Date Z (column F). Each row has a unique date range. I have filled out the an approximation of the sheet with what those formulas should return:
<tbody>
</tbody>
This spreadsheet will have several thousand individuals, each with unique date ranges, so ideally this formula would be drag-down-able. The codes (lm, rs, etc.) just need to be counted as one instance each.
I have attempted several COUNTIF functions, and have found difficulty since this formula must look at the date columns in the row of the individual as well as the date columns in the column headers. How can I do a matrixed count (if that that even a thing)? I'd be willing to try a VBA solution if folks think that is the best route.
Note:
I realize this might be more easily accomplished if I created a formula to pull down the date from the column header into the rows with the observation codes, like so:
<tbody>
</tbody>
For my column E formula, I could do: (COUNTIF(G3:L3,">="&B3)-COUNTIF(G3:L3,">="&C3))
The issue with this solution is that I really want to avoid adding formulas to practically every cell since this spreadsheet has over 100 columns and several thousand rows.
Cheers, and thanks for any thoughts you might have!
I have a spreadsheet with rows for observations of individuals. The columns B-D indicate the dates of interest. Each date range in these columns is unique to the individual. The columns (G-L) are calendar dates -- there is a column for every day for 6 months. Individuals have an observation some days, and blanks other days. I want to know the number of observations in a given, unique date range for each individual.
I need help creating a formula that counts the number of values from Date X to Date Y (column E) and from Date X to Date Z (column F). Each row has a unique date range. I have filled out the an approximation of the sheet with what those formulas should return:
| A | B | C | D | E | F | G | H | I | J | K | L | |
| 1 | ID | Date X | Date Y | Date Z | # Values from Date X to Date Y | # Values from Date X to Date Z | 3/1 | 3/2 | 3/3 | 3/4 | 3/5 | 3/6 |
| 2 | 99 | 3/1 | 3/3 | 3/5 | 2 | 4 | lm | rs | lm | rq | rs | |
| 3 | 77 | 3/2 | 3/4 | 3/6 | 3 | 4 | rs | lm | rq | lm |
<tbody>
</tbody>
This spreadsheet will have several thousand individuals, each with unique date ranges, so ideally this formula would be drag-down-able. The codes (lm, rs, etc.) just need to be counted as one instance each.
I have attempted several COUNTIF functions, and have found difficulty since this formula must look at the date columns in the row of the individual as well as the date columns in the column headers. How can I do a matrixed count (if that that even a thing)? I'd be willing to try a VBA solution if folks think that is the best route.
Note:
I realize this might be more easily accomplished if I created a formula to pull down the date from the column header into the rows with the observation codes, like so:
| A | B | C | D | E | F | G | H | I | J | K | L | |
| 1 | ID | Date X | Date Y | Date Z | # Values from Date X to Date Y | # Values from Date X to Date Z | 3/1 | 3/2 | 3/3 | 3/4 | 3/5 | 3/6 |
| 2 | 99 | 3/1 | 3/3 | 3/5 | 2 | 4 | 3/1 | 3/3 | 3/4 | 3/5 | 3/6 | |
| 3 | 77 | 3/2 | 3/4 | 3/6 | 3 | 4 | 3/2 | 3/3 | 3/4 | 3/6 |
<tbody>
</tbody>
For my column E formula, I could do: (COUNTIF(G3:L3,">="&B3)-COUNTIF(G3:L3,">="&C3))
The issue with this solution is that I really want to avoid adding formulas to practically every cell since this spreadsheet has over 100 columns and several thousand rows.
Cheers, and thanks for any thoughts you might have!
