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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!