COUNTIFS using intersection of two date periods

[rcarmichael]

Good afternoon all,
I have two named ranges: StartDate and EndDate

I wish to use a COUNTIFS function to count the instances that the following occurs in a separate table (pictured below):

• "Source.Name": AU
• "Item": ProductE5
• At least 1 day of the above date range between StartDate and EndDate (inclusive) intersects the date range in the table below "Starts On" and "Ends On" (inclusive).

The above result SHOULD be 1, as there are 3 days of crossover (16/11 - 19/11); however I can't find a formula to do this.
Currently, the incorrect formula is :

=COUNTIFS('PRICE - Special History.xlsx'!PRICE___Special_History[Source.Name],"AU",
'PRICE - Special History.xlsx'!PRICE___Special_History[Item],[@Product],
'PRICE - Special History.xlsx'!PRICE___Special_History[Starts On],">="&StartDate,
'PRICE - Special History.xlsx'!PRICE___Special_History[Ends On],"<="&EndDate)

Any ideas how I can achieve this?
Sincere regards,
Ryan

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Second suggestion to get faster/better responses: Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this is the sort of thing you are after. If so you will have to adapt workbooks, table names etc.

rcarmichael.xlsm
	A	B	C	D	E	F	G	H	I
1	Source.Name	Item	Special	Starts On	Ends On			Start Date
2	AU	Prod 1						16/11/2023
3	AU	Prod 2						End Date
4	AU	Prod 3						23/11/2023
5	AU	Prod 4
6	AU	Prod 5		28/05/2019	9/06/2019			Source.Name	AU
7	AU	Prod 5		13/08/2019	1/09/2019			Item	Prod 5
8	AU	Prod 5						Count	1
9	AU	Prod 5
10	AU	Prod 5
11	AU	Prod 5
12	AU	Prod 5
13	AU	Prod 5		22/08/2023	3/09/2023
14	AU	Prod 5		31/10/2023	19/11/2023
15
16
Sheet1

Cell Formulas
Range	Formula
I8	I8	=COUNTIFS(Table1[Source.Name],I6,Table1[Item],I7,Table1[Starts On],"<="&EndDate,Table1[Ends On],">="&StartDate)

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet1!$H$4	I8
StartDate	=Sheet1!$H$2	I8

 

[rcarmichael]

Good morning @Peter_SSs,
Firstly, thank you for your suggestions; I have updated my Account Details and installed the XL2BB Add-In for future use.
And secondly, thank you - your change in the formula works perfectly!
Sincere regards,
Ryan

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Thanks also for updating your version details.

In case you have not actually used XL2BB yet it is common that new users find that they cannot initially use it after installation. If that happens to you, first check this post:

XL2BB Icons greyed out

Hi Team, i am unable to copy the data throught Xl2bb as buttom got disable please see the screen shot can anyone hlep me this. Regards Sanjeev

www.mrexcel.com