Formula Help: Count Values within Unique Date Ranges

[kkarp]

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:


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Date X[/TD]
[TD]Date Y[/TD]
[TD]Date Z[/TD]
[TD="align: center"]# Values from Date X to Date Y[/TD]
[TD="align: center"]# Values from Date X to Date Z[/TD]
[TD]3/1[/TD]
[TD]3/2[/TD]
[TD]3/3[/TD]
[TD]3/4[/TD]
[TD]3/5[/TD]
[TD]3/6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]99[/TD]
[TD]3/1[/TD]
[TD]3/3[/TD]
[TD]3/5[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]lm[/TD]
[TD][/TD]
[TD]rs[/TD]
[TD]lm[/TD]
[TD]rq[/TD]
[TD]rs[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]77[/TD]
[TD]3/2[/TD]
[TD]3/4[/TD]
[TD]3/6[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]rs[/TD]
[TD]lm[/TD]
[TD]rq[/TD]
[TD][/TD]
[TD]lm[/TD]
[/TR]
</tbody>[/TABLE]










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:


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Date X[/TD]
[TD]Date Y[/TD]
[TD]Date Z[/TD]
[TD]# Values from Date X to Date Y[/TD]
[TD]# Values from Date X to Date Z[/TD]
[TD]3/1[/TD]
[TD]3/2[/TD]
[TD]3/3[/TD]
[TD]3/4[/TD]
[TD]3/5[/TD]
[TD]3/6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]99[/TD]
[TD]3/1[/TD]
[TD]3/3[/TD]
[TD]3/5[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3/1[/TD]
[TD][/TD]
[TD]3/3[/TD]
[TD]3/4[/TD]
[TD]3/5[/TD]
[TD]3/6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]77[/TD]
[TD]3/2[/TD]
[TD]3/4[/TD]
[TD]3/6[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]3/2[/TD]
[TD]3/3[/TD]
[TD]3/4[/TD]
[TD][/TD]
[TD]3/6[/TD]
[/TR]
</tbody>[/TABLE]









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!

 

[Peter_SSs]

I've used the formula that cb366374 corrected a...

Which is identical to the formula I originally posted, adjusted for the extra columns not specifically identified in post #1 and also for the fact that your actual headers are in row 2 with data from row 3, whereas in post #1 you identified headers as row 1 & data from row 2.

Anyway, good to hear it is resolved. Thanks for letting us know.

 

[kkarp]

Anyway, good to hear it is resolved. Thanks for letting us know.

No, thank you! Follow-up question -- the formula is working fantastically for me, but I'm having a hard time dissecting exactly what the formula "says". I'm struggling to parse out how the formula is functioning.

Formula:
=COUNTIFS($G$2:$HR$2,">="&$B3,$G$2:$HR$2,"<="&C3,$G3:$HR3)

I tried to translate it (see the nonsensical thing below) and I'm clearly not totally following what's happening here.
=Count if ("Cells in date range", "are greater than or equal to Date X (plus?) Date Range", "are less than or equal to Date Y (plus?) values I want to count")

 

[Peter_SSs]

.. but I'm having a hard time dissecting exactly what the formula "says".

Formula:
=COUNTIFS($G$2:$HR$2,">="&$B3,$G$2:$HR$2,"<="&C3,$G3:$HR3)

That is only part of the formula.

The syntax for COUNTIFS is
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
and the function counts how many times all criteria are met.

For your circumstance there are 3 such pairs of range & criteria
=COUNTIFS($G$2:$HR$2,">="&$B3,$G$2:$HR$2,"<="&C3,$G3:$HR3,"<>")

So we count how many times ..

"Cells in date range", "are greater than or equal to Date X"
and
"Cells in date range", "are less than or equal to Date Y"
and
"Cells in the data range", "are not equal to blank"

 

[kkarp]

Wow, thank you for laying that out so clearly! This has been immensely helpful! Peter, you're fantastic! Thanks again for your super speedy and thorough help!

 

[Peter_SSs]

You are very welcome.