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

[email protected]

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

Paul Newcome

Are you able to provide some screenshots for reference?

[email protected]

Hi Paul,

Does this help?

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.

[email protected]

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

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

[email protected]

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