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?
-
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.
-
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, ...............................)
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!