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!

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.