How to create a summary (count) of open tasks across sheets by contact

ilene_healy68056

Hi

I have found lots of others with similar questions, but I don't seem to quite find the solution that would work for me. I have a project worksheet and an action log worksheet with contacts listed as the "Assigned To". I would like to create a summary (like an Excel pivot) of how many actions are open, closed, escalated by contact Assigned To. I wish this could be a user-friendly canned report function :-(

Is there any advice to do an index+match+count using a contact field type to get a count such as this (for a non-coder type)? Some of my fields have 2 contacts, so that adds another layer of complexity.

Please any advice to get me started would be soooo awesome.

The first image is my summary sheet and the next one is one of my detail sheets - the action log

Paul Newcome

Try a COUNTIFS similar to:

=COUNTIFS({Other Sheet Column to Count}, "Closed", {Other Sheet Contact Column}, FIND("John Doe", @cell) > 0)

Paul Newcome

Try a COUNTIFS similar to:

=COUNTIFS({Other Sheet Column to Count}, "Closed", {Other Sheet Contact Column}, FIND("John Doe", @cell) > 0)

ilene_healy68056

Thanks Paul. This solved my multiple contact problem. I appreciate your help sooo much.

thanks

Paul Newcome

Happy to help! 👍️

ilene_healy68056

Hi Paul,

I was successful in my formula to count the open actions by contact. I wanted to add to that what open actions by contact are due WITHIN the next 7 days. I started adding an AND statement around due date and TODAY() and am getting #invalid operation. Everything works until I insert this piece into it: AND({Action Log - Digital Logistics Due Date} >= TODAY(), {Action Log - Digital Logistics Due Date} <= TODAY(7)).

Any ideas where my mistake might be? Here is my complete formula

=COUNTIFS({Action Log - Digital Logistics Status}, <>"Full", AND({Action Log - Digital Logistics Due Date} >= TODAY(), {Action Log - Digital Logistics Due Date} <= TODAY(7)), {Action Log - Digital Logistics Assigned To}, FIND("Keith Lawlor", @cell) > 0)

thanks for your help

Ilene

Paul Newcome

You are going to wan to use the syntax of

{Range}, AND(................)

=COUNTIFS({Other Sheet Column to Count}, "Closed", {Other Sheet Contact Column}, FIND("John Doe", @cell) > 0, {Date Range}, AND(@cell >= TODAY(), @cell <= TODAY(7)))