Subset Value within a Date Range

[JoeExcelHelp]

Hi, hope everyone is well

Hans, thanks for the site assistance

I'm trying to count a subset value of a max value based on ascending date criteria.

I attached a workbook that I hope provides some better clarity but please reach out if you need it explained better

Thank You

200505 Subset within max value.xlsx

[HansV]

I think your example is incorrect - D16 shouldn't be blue (it belongs to BOS instead of JFK); D18 should be blue. This changes the counts for JFK.

As an array formula confirmed with Ctrl+Shift+Enter in I10:

=COUNTIFS($B$5:$B$23,$G10,$D$5:$D$23,I$9,$E$5:$E$23,"<="&SMALL(IF($B$5:$B$23=$G10,$E$5:$E$23),$H10))

Fill right to J10, than down to row 11.

[JoeExcelHelp]

Thanks, Hans

It doesnt seem to be counting correctly but I added a criteria and that may be influencing the value
I tried it also without the added criteris and the count is still incorrect
The attached WB contains the exact format

200505 Subset within max value v3.xlsx

[JoeExcelHelp]

Sorry Hans please reference this WB

200505 Subset within max value v3.xlsx

[JoeExcelHelp]

Apologies Hans for the multiple WBs
This WB is correct and please reference this
Thank You

200505 Subset within max value v3.xlsx

[HansV]

I was busy analyzing your previous workbook when you posted a new one...

- You didn't incorporate the condition on column B in the SMALL part. That is easily solved:

=COUNTIFS(Interest!$C:$C,$F2,Interest!$A:$A,I$1,Interest!$B:$B,$G2,Interest!$D:$D,"<="&SMALL(IF((Interest!$C:$C=$F2)*(Interest!$B:$B=$G2),Interest!$D:$D),$H2))

- I suspect the real problem is with ties.

Let's take ALB as example. These are the data for Albany

S3285.png

The row for ALB on the formula sheet has 3 as Sum of Value.
As you see, the dates 10/21/15 and 01/06/16 occur twice each, so if we let Excel include the earliest three of them, it picks all four. There is no way for Excel to decide which one not to include.

[JoeExcelHelp]

Thank Hans,

I may need to modify conditions where the date is the same

Its strange in that some values are correct and others are not
I appended JFK which shows zero but records exist

200505 Subset within max value v3.xlsx

[HansV]

That is because the value in column H is larger than the number of rows for JFK/AO and JFK/GO.
Change the formula in I2 to

=COUNTIFS(Interest!$C:$C,$F2,Interest!$A:$A,I$1,Interest!$B:$B,$G2,Interest!$D:$D,"<="&SMALL(IF((Interest!$C:$C=$F2)*(Interest!$B:$B=$G2),Interest!$D:$D),MIN($H2,COUNTIFS(Interest!$C:$C,$F2,Interest!$B:$B,$G2))))

In rows where the number in column H is larger than the number of corresponding rows on the other sheet, the sum of columns I and J will be different from the number in column H.

[JoeExcelHelp]

Thank You Hans