Sum total unique overlapping days within multiple ranges, based on a condition

[plshalp]

In this table I have multiple flights of a campaign that may or may not run on overlapping dates. The date range of the flights are denoted in the AB (start) and AC columns (end).

The intent is to sum all days in the date ranges relative to a campaign, in a way that no date is duplicated, to get the total aggregated daily runtime of a campaign. This can then be used to calulate the pacing column by comparing what has actually happened to what should have happened assuming an even pace.

Currently the "pacing" in the AB column is incorrectly showing twice as much as it should be. This is because the campaign_duration column and days remaining_column are not de-duplicating the duplicate date ranges in a campaign. I also don't know how to account for periods such as 8/07/2022 - 10/07/2022 which run in parallel to the longer flight above it, creating another layer of duplication inaccuracy when calculating the total deduplicated days across all period in a campaign.

The formula in question is in column W which basically sums the product of all start and dates of a flight relating to a campaign:

=IF(ISNUMBER(SEARCH("Using Campaign budget",I2)),SUMPRODUCT(($AD$2:$AD$10000-$AC$2:$AC$10000)*($T$2:$T$10000=T2)),N2-(M2-1))

If I add in UNIQUE():

=SUMPRODUCT(UNIQUE(AD2:AD9-AC2:AC9))

It seems to de-duplicate the duplicate date ranges but as soon as I add the campaign conditional to the end of the SUMPRODUCT formula:

*($T$2:$T$10000=T2)),N2-(M2-1))

I get #N/A in all cells.

All of this really starts to slow down once I add in a few thousand lines as well, can anyone help provide a better solution, or at least a way to deduplicate the repeating ranges with this method?

close.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Account name	Campaign name	Ad Set or Insertion Order	Reach	Impressions	Frequency	Currency	Amount spent	Ad set budget	Ad set budget type	Campaign budget	Campaign budget type	Starts	Ends	Reporting starts	Reporting ends		prog_lead	advertiser	campaign	ad_set_or_io	platform	campaign_duration	days_passed	days_remaining	expected_budget_spent	actual_budget_spent	pacing	line_start	line_end	modified_line_end
2	Test Account	Campaign A	Targeting A	261757	1200753	4.58728133	NZD	9440	Using campaign budget	Lifetime	15000	Lifetime	14/07/2022	30/10/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting A	Facebook	346	236	110	10231.21387	20951.24	205%	14/07/2022	30/10/2022	30/10/2022
3	Test Account	Campaign A	Targeting B	25344	63922	2.52217487	NZD	568.16	Using campaign budget	Lifetime	15000	Lifetime	31/10/2022	31/12/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting B	Facebook	346	236	110	10231.21387	20951.24	205%	31/10/2022	31/12/2022	6/11/2022
4	Test Account	Campaign A	Targeting C	32945	46220	1.4029443	NZD	241.92	Using campaign budget	Lifetime	15000	Lifetime	11/07/2022	13/07/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting C	Facebook	346	236	110	10231.21387	20951.24	205%	11/07/2022	13/07/2022	13/07/2022
5	Test Account	Campaign A	Targeting D	17533	26168	1.49249986	NZD	225.54	Using campaign budget	Lifetime	15000	Lifetime	8/07/2022	10/07/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting D	Facebook	346	236	110	10231.21387	20951.24	205%	8/07/2022	10/07/2022	10/07/2022
6	Test Account	Campaign A	Targeting E	261757	1200753	4.58728133	NZD	9440	Using campaign budget	Lifetime	15000	Lifetime	14/07/2022	30/10/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting E	Facebook	346	236	110	10231.21387	20951.24	205%	14/07/2022	30/10/2022	30/10/2022
7	Test Account	Campaign A	Targeting F	25344	63922	2.52217487	NZD	568.16	Using campaign budget	Lifetime	15000	Lifetime	31/10/2022	31/12/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting F	Facebook	346	236	110	10231.21387	20951.24	205%	31/10/2022	31/12/2022	6/11/2022
8	Test Account	Campaign A	Targeting G	32945	46220	1.4029443	NZD	241.92	Using campaign budget	Lifetime	15000	Lifetime	11/07/2022	13/07/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting G	Facebook	346	236	110	10231.21387	20951.24	205%	11/07/2022	13/07/2022	13/07/2022
9	Test Account	Campaign A	Targeting H	17533	26168	1.49249986	NZD	225.54	Using campaign budget	Lifetime	15000	Lifetime	8/07/2022	10/07/2022	1/01/2022	6/11/2022		Bob	Test Account	Campaign A	Targeting H	Facebook	346	236	110	10231.21387	20951.24	205%	8/07/2022	10/07/2022	10/07/2022
Sheet1

Cell Formulas
Range	Formula
S2:U9	S2	=A2
W2	W2	=IF(ISNUMBER(SEARCH("Using Campaign budget",I2)),SUMPRODUCT(($AD$2:$AD$10000-$AC$2:$AC$10000)*($T$2:$T$10000=T2)),N2-(M2-1))
X2:X9	X2	=W2-Y2
Y2	Y2	=SUMPRODUCT(($AD$2:$AD$10000-$AE$2:$AE$10000)*($T$2:$T$10000=T2))
Z2:Z9	Z2	=(IF(ISNUMBER(SEARCH("using ad set budget",K2)),I2,K2)/W2)*X2
AA2:AA9	AA2	=IF(ISNUMBER(SEARCH("Using ad set budget",K2)),H2,SUMIFS(H:H,I:I,"*Using campaign budget",B:B,B2))
AB2:AB9	AB2	=AA2/Z2
W3:W9	W3	=IF(ISNUMBER(SEARCH("Using Campaign budget",I3)),SUMPRODUCT(($AD$2:$AD$1048576-$AC$2:$AC$1048576)*($T$2:$T$1048576=T3)),N3-(M3-1))
Y3:Y9	Y3	=SUMPRODUCT(($AD$2:$AD$1048576-$AE$2:$AE$1048576)*($T$2:$T$1048576=T3))
AE2:AE9	AE2	=IF(P2>AD2,AD2,P2)