Help needed with building multi dimensional formula

[AKuma0411]

Hi Team,

I need help building a logic in excel, here’s what I’m trying to do:

I have two tabs in excel, sheet 1 and the other is sales tab,

 In sheet 1, I have following data points: The dates are in form of MMDDYYYY

Now, I want the logic to always pull sales based on Tracking start and end dates for DSeA first, second SCC, and lastly the SV engagement.

So, it should start pulling the sales for DSeA for month starting from February to October

Then for SCC it should just pull sales for Nov to Dec as there is overlapping time periods, I don’t want the logic to double count it for both the engagement type.

Lastly, for SV it should just pull the sales for Jan of 2023 as I don’t want to double count the 

overlapping time for this engagement type as well.

The hierarchy will always be DSeA first based on start and end date next SCC and at last the SV engagement

Sales Tab:

[#2]

@AKuma0411 

Hi,

Is the attached in line with your logic?

 

[#3]

@rachel 

Thanks for your help, appreciated!! this logic is giving me the result I need in column H, just have a quick  question- this logic right now is pulling values just for one UCN, and I have around10,000+ line items for different UCns, Is there a way to tweak this logic so that it works for range of UCNs 

Thank you!

[#4]

@AKuma0411 

Hi,

Will the attached work?

I just assume your data will always be in this order:

Row1: DSeA, Row2: SCC, Row3: SV

If you don’t want DSeA, you just need to enter Measure Start Date = 31/12/2023, and Measure End Date = 01/01/2023, the resulting YTD will be zero.

Similar for SCC, SV. Like below: