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
-
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.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!