Sheet report Countifs - multiple categories by person

Jason P

Hi

We use a metrics sheet to tally the number of design dept. requests (10 types), this then feeds to a dashboard for the Co. to see currents Dept demands. I've been asked to now reflect the number of requests by status (7 types) per design team member (4 staff).

I have individual totals, IE David 10, =COUNTIF({Request Tracker Range 3}, Category25. but can't work out the formula required to show the status Eg: of David's 10, 3 are In progress, 2 are Pending, 3 Not started etc etc. The source sheet has the columns next to each other "Assigned To" & "Status", everything I try I get different error's, help! Thanks Team.

Regards

Jason.

Paul Newcome

You are going to want to reference the other sheet's columns individually like so...

=COUNTIFS({Other Sheet Assigned To}, "David", {Other Sheet Status Column}, Category@row)

Paul Newcome

Can you provide screenshots of your source and metrics sheet with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

You are most likely going to end up using a COUNTIFS function, but I can't be more specific than that without seeing what exactly you are working with.

Jason P

Morning Paul,

Thanks for reaching out, see attached. I have a sheet summary set up in the Design 'RR' that is counting total of each Status category but this should not be effecting the metrics sheet. As you can see the current Fx is counting Dept. status totals and not for David. I have 3 other staff members I need to account the same to.

Regards

Jason.

Paul Newcome

You are going to want to reference the other sheet's columns individually like so...

=COUNTIFS({Other Sheet Assigned To}, "David", {Other Sheet Status Column}, Category@row)

Jason P

Thanks Paul,

Worked perfectly

Paul Newcome

Happy to help! 👍️