Countifs with multiple references

Mac Choi

Hello,

I'm in the process of making resource plan worksheet and it needs data from project plan worksheet.

Currently I have tried to pull the data for 'Assigned', 'Open' and 'Delayed' tasks from counting on project plan worksheet and formula used is as follows.

It would be appreciated if you guys can give me a hand to figure this out!

-----

=COUNTIFS({Project Plan - Task}, <>"", {Project Plan - Task Owner / Task Support}, Email@row))

=COUNTIFS({Project Plan - Task}, <>"", {Project Plan - Status}, <>"Completed", {Project Plan - Status} <> "Cancelled", {Project Plan - Task Owner / Task Support}, Email@row)

=COUNTIFS({Project Plan - Task}, <>"", {Project Plan - Status}, "Delayed", {Project Plan - Task Owner / Task Support}, Email@row)

DKazatsky2

Hi @Mac Choi ,

For the first 2 formulas, try this.

=COUNTIFS({Project Plan - Task}, <>"", {Project Plan - Task Owner / Task Support}, Email@row)

=COUNTIFS({Project Plan - Task}, <>"", {Project Plan - Status}, AND(@cell <> "Completed", @cell <> "Cancelled"), {Project Plan - Task Owner / Task Support}, Email@row)

The third formula should be working as is, see if it sorts itself out when the first 2 are fixed.

Hope this helps,

Dave

Mac Choi

Hello Dave,

Thanks for advice, but it still take me to the 'INCORPORATE ARGUMENT SET" for all 3 items..

I used to do an excel a lot, but this system is little bit different..

Mac Choi

When I choose only one reference sheet and condition, it's properly working...

=COUNTIFS({Project Plan - Task Owner / Task Support}, Email@row)

Paul Newcome

Double check that your references have all been created properly. Sometimes there can be a slight lag in the window that is used to create the cross sheet reference. If you select your range too quickly, it can revert to the cell in the top left corner of the reference sheet once the lag clears.

Mac Choi

Thanks Paul and Dave,

Thankfully, I somehow figure out the issue as I chose 2 nearby columns at the same time for the reference and it was properly working as I chose sole column instead.

Thanks for all your comment!!

Paul Newcome

Happy to help. 👍️