COUNTIF Across Multiple Sheets

brianw

Hi All

I am trying to get a count on total number of overdue items on a different sheet by project manager. I have tried multiple variations of the below formula but have yet to find one that works.

=COUNTIF(AND({Data Range 1}, CONTAINS("Carolyn Kuo", @cell), {Data Range 1} = "Carolyn Kuo"))

=COUNTIF({Data Range 1}, CONTAINS([Project Manager]@row, @cell)), AND({Data Range 3} = "Overdue")

=COUNTIFS({Data Range 3}, CONTAINS("Overdue", @cell), {Data Range 1}, CONTAINS("Carolyn Kuo"))

Basically, if the Project Manager Column on the sheet "Data" contains a specific project managers name and the status column on the sheet "Data" states Overdue I want it to count on a different sheet. I can get it to count the number of times a Project Managers name appears on the sheet "Data", but when I add the overdue component I get errors.

I dont feel like this should be that hard to do but I cannot seem to figure it out.

brianw

Nevermind I figured it out

=COUNTIFS({Data Range 1}, CONTAINS([Project Manager]@row, @cell), {Data Range 3}, CONTAINS("Overdue", @cell))

Andrée Starå

Hi @brianw

I hope you're well and safe!

Excellent!

Glad that you figured it out!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

brianw

Thanks Andrée!

I do have another question you may be able to assist with along these same lines. I want to pull the same data, but look at the assigned due dates and break the overdue items into columns by how long they have been overdue (i.e., <30 days, 30-60, 60-90, 90+). I am having trouble getting the date range to work. For the 30 days I am using this, which is returning a value, but not the correct one.

=COUNTIFS({Data Range 1}, CONTAINS([Project Manager]@row, @cell), {Data Range 3}, CONTAINS("Overdue", @cell), {Data Range 1}, <TODAY(30))

And then trying this for 30-60, but its returning no values, which isnt correct either.

=COUNTIFS({Data Range 1}, CONTAINS([Project Manager]@row, @cell), {DataRange 3}, CONTAINS("Overdue", @cell), {Data Range 1}, >TODAY(30), {Data Range 1}, <TODAY(60))