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
-
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!
-
Hi Paul,
Does this help?
-
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!
-
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)))
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!