Need to count "time allotment" If certain statements are True

Hello,

I am trying to count the "time allotment" using the below restrictions. The below formula counts how many meetings each person has, I am trying to add another one to count how many hours of meetings they are in.

Working Formula for how many meetings: =COUNTIFS({Editor}, HAS(@cell, Metric@row), {Type}, "Meeting", {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), {Start Date Estimated}, AND(@cell <= TODAY(+7)))

What I have tried so far, that does not work.

=COUNTIFS({Editor}, HAS(@cell, Metric@row), {Type}, "Meeting", {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), {Start Date Estimated}, AND(@cell <= TODAY(+7)), COUNT({Time Allotment}))

=COUNTIFS({Editor}, HAS(@cell, Metric@row), {Type}, "Meeting", {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), {Start Date Estimated}, AND(@cell <= TODAY(+7)), COUNTM({Time Allotment}))

=COUNT({time allotment}), =COUNTIFS({Editor}, HAS(@cell, Metric@row), {Type}, "Meeting", {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), {Start Date Estimated}, AND(@cell <= TODAY(+7)))

=COUNT({Time Allotment}),=IF({Editor}, HAS(@cell, Metric@row), {Type}, "Meeting", {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), {Start Date Estimated}, AND(@cell <= TODAY(+7)))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are wanting a total of the Time Allotment column based on the above criteria, then you are going to want to look into the SUMIFS function.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Paul,

    I am still not sure what I am doing wrong, can you take a look at the below formula?

    =SUMIF({Time Allotment}, IF({assignee}, "meeting", {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), HAS(@cell, Metric@row), {Start Date Estimated}, AND(@cell <= TODAY(+7))))

    or

    =SUMIF({Time Allotment}, {assignee}, AND(@cell = "meeting"), {Meeting Status}, AND(@cell <> "Cancelled", @cell <> "Complete"), HAS(@cell, Metric@row), {Start Date Estimated}, AND(@cell <= TODAY(+7)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to want to take the same range/criteria sets that you are using for the working COUNTIFS, and use those for the range/criteria sets in a SUMIFS.


    =SUMIFS({range to sum}, range, criteria, range, criteria, ...............................)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you! Can you also help me with %Complete?

    Looking to understand the %Complete based off of "Start Date Estimated" & "End Date Estimated".

    I dont know if multiple fields need to be updated, or not.

    Field 1 - Duration End Date Estimated - Start Date Estimated

    Field 2 - Today - End Date Estimated

    Field 3 - duration vs today - End Date

    Field 4 - End Date Minus Total/ Total Duration Days %

    Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!