Urgent Help Needed by Formula Gurus - Adding Multiple COUNTIFS statements

Shannon Heward

I have a complex formula where I'm trying to add two COUNTIFS statements together [in the future I will want to do multiple statements]. The formula is only including the value of the first part of the formula and not the second. And, if I reverse the formula it still only returns the value only for the first part of the formula [upon reversal I'm getting a different value]. Please help!! Let me know if you have any questions. Thank you so much!

=IF(COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0, COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) + IF(COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0, COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement"))))

Jen Lange

@Shannon Heward try this... assuming that all the smaller segments within the COUNTIFS are accurate.

=IF(COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0, COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")),"")

=IF(COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0,COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement"))

Jen Lange

@Shannon Heward , it appears written correctly at first glance and the fact that you're not receiving a UNPARSEABLE error. Any time I'm not receiving the expected result for a complex formula, I validate each individual part.

Have you tried breaking up your formula into the basic parts to confirm each section calculates correctly? For instance, do these individual parts give you the expected response?

First check initial count result:

COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")

Then check the ">0" result:

=IF(COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0

Then check the second count result:

COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement")

Then check the second ">0" result:

IF(COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0

Otherwise, I'm not seeing the closure ")" of your IF statements or the concluding [value_if_false] and then remove the last two parenthesis.

=IF(TRUE, 5,”") + IF(TRUE, 3,””)

I hope this helps you troubleshoot. I cannot provide much feedback beyond this, without actually touching the formula in your sheet.

Shannon Heward

=IF(COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0

=IF(COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0

Thank you so much!! These above are giving me "INCORRECT ARGUMENT"...what do I need to do to fix?

Shannon Heward

I want a blank cell if the value is 0. I want the actual value if greater than 0.

Jen Lange

@Shannon Heward try this... assuming that all the smaller segments within the COUNTIFS are accurate.

=IF(COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0, COUNTIFS({Agreements Tracker [Shannon] Range 4}, ="Distribution Agreement", {Agreements Tracker Sheet [Shannon] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker Sheet [Shannon] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker Sheet [Shannon] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Shannon] Range 4}, NOT(@cell = "Non-Disclosure Agreement")),"")

=IF(COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement")) > 0,COUNTIFS({Agreements Tracker [Sandra Jackson] Range 4}, ="Distribution Agreement", {Agreements Tracker [Sandra Jackson] Range 2}, HAS(@cell, "Amy Lin-Furukawa"), {Agreements Tracker [Sandra Jackson] Range 3}, CONTAINS("SRTC", @cell), {Agreements Tracker [Sandra Jackson] Range 1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for Requestor Response"), {Agreements Tracker [Sandra Jackson] Range 4}, NOT(@cell = "Non-Disclosure Agreement"))

Shannon Heward

Thank you so much!! This is working great!! I really appreciate your help!!

Jen Lange

That's great! Happy to help. I'm glad it's working for you.

Jen Lange

@Shannon Heward , thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time.

If you appreciate my response, please recognize the effort with an "Insightful" or "Vote Up" selection. Thanks!

Happy Friday 😊

Shannon Heward

Yes, thank you!! I don't quite know the difference, but I chose "Insightful"! :)