matricial multricriterias sum

Bob21

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!

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.

Bob21

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

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.

Bob21

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

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))