matricial multricriterias sum

Dear Community,


I exhausted all my ideas. I'm using smartsheet to follow a project.

Based on the "start / end date" and the "assigned to" field I'm trying to sum the availability of the ressources.

To do so I'm referencing another sheet in which I have my team planning (row = date and column = people), if they are in holidays I put a 0 or 1 if working.

To calculate the sum of day for which each "assigned to" is available during the project (between start and end date), I tried:

Sum(Index(,equiv(),equiv() :Index(),equiv(),equiv() )

=SUM(

INDEX({TechCare holidays Range 1}, MATCH([Start Date]@row, {TechCare holidays Range 2}, 0), MATCH([Assigned To]@row, {TechCare holidays Range 3}, 0))

:

INDEX({TechCare holidays Range 1}, MATCH([End Date]@row, {TechCare holidays Range 2}, 0), MATCH([Assigned To]@row, {TechCare holidays Range 3}, 0))

)

While this work fine on excel I receive an uparseable error

I also tried to use a sumifs function including an index(,equiv()) to select the correct people (assigned to).

=SUMIFS(

INDEX({TechCare holidays Range 7}, 0, MATCH([Assigned To]@row, {TechCare holidays Range 3},0)),{TechCare holidays Range 5},">="&[Start Date]@row,

{TechCare holidays Range 5},"<="&[End Date]@row)

Again it works fine on excel but I receive an uparseable error.


Can you please guys help me, I'm loosing my hair!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What are the different ranges each referencing?


    I feel like you should be able to use a basic SUMIFS.

    =SUMIFS({Reference Sheet 1/0 Column}, {Reference Sheet Date Column}, AND(@cell>= [Start Date]@row, @cell<= [ENd Date]@row, {Reference Sheet Name Column}, @cell = [Assigned To]@row)


    If you can provide screenshots with sensitive data blocked, that would be really helpful.

  • Thanks a lot Paul,


    I can't make it works on my side. Here are screenshots of my ranges and formula:


    It returns unparseable.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You do not need an INDEX function and the way your overall syntax is not correct. Try using the exact formula I typed out and set each range to only be the column listed.

  • Thanks again Paul, but I want my assigned to column to be dynamic since I have a ton of them.

    If I'm not mistaken, for your formula to work I need to define each time the correct column for a sum.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to use a nested IF statement inside of the first portion of the SUMIFS.


    =SUMIFS(IF([ASsigned To]@row = "Bob", {Bob's Column}, IF([Assigned To]@row = "Toto", {Toto's Column})), {Reference Sheet Date Column}, AND(@cell>= [Start Date]@row, @cell<= [ENd Date]@row))


    The easier way to do this would be to have each person for each date listed in the same cell. You can use a formula for this

    =JOIN(COLLECT([P1]$1:[P(last_#)]$1, [P1]@row:[P(last_#]@row, @cell = 1), CHAR(10))


    Then you can use this in place of the SUMIFS

    =COUNTIFS({Join Column}, HAS(@cell, [Assigned To]@row, {Reference Sheet Date Column}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!