TIMESUM with LOOKUP

JBeauchs
JBeauchs
in Modeling

I have an issue where I am creating a fillable form for a client that includes entering a start and end date. So the user would chose a client and then a product, enter start and end dates and I need to then sum the revenue based on those choices. So, ideally I would be able to TIMESUM based on the dates chosen and LOOKUP the client and product. From what I understand I cant use TIMESUM with LOOKUP so what is the workaround? 

It's been suggested to use a staging module but the dates are user input so I am not sure how that would work?

Tagged:

Answers

  • andrewtye
    andrewtye

    Hi @JBeauchs 

    Would have module a that's dimensioned with time and does the lookup for client/product selection and then do the timesum off the back of this.

    You can do the other way around but is likely to create a larger module combination than is needed.

    Andrew

  • carldent
    carldent
    edited November 2023

    Hello gorilla tag, I think in a cell, you should use the LOOKUP function to find the revenue associated with the chosen client and product. For example, if the client is in cell A2 and the product is in cell B2, you can use a formula like:

    =LOOKUP(2,1/((ClientRange=A2)*(ProductRange=B2)),RevenueRange)

    ```

    Replace `ClientRange`, `ProductRange`, and `RevenueRange` with the appropriate ranges in your data table.

    Then, in another cell, use the SUMIFS function to sum the revenue based on the chosen start and end dates, as well as the client and product. For example, if your start date is in cell C2, end date is in cell D2, client is in cell A2, product is in cell B2, and the revenue range is in column E, you can use a formula like:

    =SUMIFS(RevenueRange,ClientRange,A2,ProductRange,B2,StartDateRange,">="&C2,EndDateRange,"<="&D2)

    Replace `ClientRange`, `ProductRange`, `RevenueRange`, `StartDateRange`, and `EndDateRange` with the appropriate ranges in your data table.

Categories