COUNTIFS, IFS & OR Function

Shannon Heward

I'm having difficulty getting this formula to work. I'm trying to pull data from several sheets together if they are a certain contract type and a contract status and blank if the count is 0. Could someone please help? Thank you!

=IF(COUNTIFS({Sha3}, ="Distribution", {Sha1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Sha3}, ="Distribution", {Sha1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({San3}, ="Distribution", {San1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({San3}, ="Distribution", {San1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({Ann3}, ="Distribution", {Ann1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Ann3}, ="Distribution", {Ann1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({Ang3}, ="Distribution", {Ang1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Ang3}, ="Distribution", {Ang1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({Dav3}, ="Distribution", {Dav1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Dav3}, ="Distribution", {Dav1}, OR(@cell = "Customer Legal Review,", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({Ale3}, ="Distribution", {Ale1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Ale3}, ="Distribution", {Ale1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({Sis3}, ="Distribution", {Sis1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Sis3}, ="Distribution", {Sis1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) + IF(COUNTIFS({Ste3}, ="Distribution", {Ste1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response")) > 0, COUNTIFS({Ste3}, ="Distribution", {Ste1}, OR(@cell = "Customer Legal Review", @cell = "Pending PAC", @cell = "Pending SRTC", @cell = "Sent for Signatures", @cell = "WD Legal Review", @cell = "Waiting for WD Response"))))))))))

Jeff Reisman

My personal best practice is to never do that 🔼 😀 It's almost impossible to troubleshoot!

Instead, I would recommend a helper sheet with rows for each of the sheets you're counting the data from. Collect your data from each sheet, and then compile your final counts by adding up the individual sheet results. You will greatly increase your flexibility with your formulas and allow you to find mistakes in your data collection a lot more easily.

So for example, you'd have a Distribution column for counts for "Distribution" from Sheet A, then from Sheet B in the next row, then Sheet C, etc. Next column for counts of "Customer Legal Review" from Sheet A, Sheet B, Sheet C, etc. and so on. Then at the bottom of your sheet you just SUM the column values above it to get the totals for each type.