Forum Discussion

AKuma0411's avatar
AKuma0411
Brass Contributor
Mar 05, 2024

Help needed with building multi dimensional formula

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:

 

Formulas and Functions
    • AKuma0411's avatar
      AKuma0411
      Brass Contributor
      Mar 05, 2024

      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!

      • rachel's avatar
        rachel
        Steel Contributor
        Mar 06, 2024

        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:

         

         

Resources