Formula using AND and OR functions

deb_63_hydracor

Hello,

I'm trying to build a formula to count the number of deployments in queue by month.

All of these criteria must be met:

• Project Complete is unchecked
• Project Cancelled is unchecked
• Row creation month is June
• Row creation year is 2022

And, deployment type is either New Deployment or Package Upgrade.

I started with this formula but don't know where the OR should go or if I also need to use AND:

Thank you!

Paul Newcome

Lets try referencing the created date directly...

=COUNTIFS({Project Complete}, @cell <> 1, {Project Cancelled}, @cell <> 1, {Deployment Type}, OR(@cell = "New Deployment", @cell = "Package Upgrade"), {Sodexo Deployment Queue Row Creation Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2022))

Paul Newcome

Try this:

=COUNTIFS(........., {Deployment Type}, OR(@cell = "New Deployment", @cell = "Package Upgrade"), ..............)

deb_63_hydracor

I'm getting "Invalid Data Type"

I know that the type of column can make this error (if not a text/number column), however I've used those columns (some of them drop-down) in many other formulas which DO work.

deb_63_hydracor

This is my new formula based on your response above:

Paul Newcome

Double check all of your ranges to ensure that error is not present in any cell in the source data.

deb_63_hydracor

I do not see any errors in any of the cells in any of the ranges.

Paul Newcome

Ok. Try using "@cell" references for all of your criteria.

@cell = 0

@cell = "June"

etc.

deb_63_hydracor

I still got an error when I entered @cell=0 so I tried it with quotes around the 0. Now, no errors but I'm getting a result of 0 for all months (after changing the month in the formula for the applicable row).

This is the formula I'm using:

=COUNTIFS({Deployment Queue Project Complete}, @cell = "0", {Deployment Queue Project Cancelled}, @cell = "0", {Deployment Type}, OR(@cell = "New Deployment", @cell = "Package Upgrade", {Sodexo Deployment Queue Row Creation Month}, @cell = "June", {Sodexo Deployment Queue Year}, @cell = "2022"))

This is a formula referencing the same sheet that works (though has fewer criteria). Thought this might help?

=COUNTIFS({Sales & Leads}, 1, {Sodexo Deployment Queue Row Creation Month}, "June", {Sodexo Deployment Queue Year}, "2022")

Thank you!

Paul Newcome

Lets try this:

=COUNTIFS({Deployment Queue Project Complete}, @cell <> 1, {Deployment Queue Project Cancelled}, @cell <> 1, {Deployment Type}, OR(@cell = "New Deployment", @cell = "Package Upgrade", {Sodexo Deployment Queue Row Creation Month}, @cell = "June", {Sodexo Deployment Queue Year}, @cell = "2022"))

deb_63_hydracor

Now I'm getting "Invalid Data Type" error again. This is perplexing!

Paul Newcome

I just noticed a misplaced parenthesis. The OR function hasn't been closed out properly.

=COUNTIFS({Deployment Queue Project Complete}, @cell <> 1, {Deployment Queue Project Cancelled}, @cell <> 1, {Deployment Type}, OR(@cell = "New Deployment", @cell = "Package Upgrade"), {Sodexo Deployment Queue Row Creation Month}, @cell = "June", {Sodexo Deployment Queue Year}, @cell = "2022")

deb_63_hydracor

No error now but I'm getting a result of 0 again for all months. I believe I figured out the problem however don't know how to fix it.

Project Complete is unchecked OR Project Cancelled is unchecked (I missed this "or" in my original message 😔

• Row creation month is June
• Row creation year is 2022
• Deployment type is either New Deployment OR Package Upgrade

Paul Newcome

Ah. Ok. That presents another challenge because it is an OR for two different ranges which can't be done within a single COUNTIFS. You have two options...

You can write out two COUNTIFS (one for each range being unchecked) and add them together, or you can insert a helper column on the source sheet with a formula to check a box if the row meets that particular bit of criteria and then include the new column in your single COUNTIFS.

deb_63_hydracor

Thank you Paul. As I'm sure you're not surprised, I was not able to put it all together without an error. The first "phrase" is for Project Cancelled; the second is for Project Complete.

=COUNTIFS(OR({Deployment Queue Project Cancelled}, @cell="0",{Deployment Type},OR(@cell="New Deployment",@cell="Package Upgrade", {Sodexo Deployment Queue Row Creation Month},@cell="June",{Sodexo Deployment Queue Year},@cell="2022") OR

=COUNTIFS({Deployment Queue Project Complete},@cell="0",{Deployment Type,OR)@cell="New Deployment",@cell="Package Upgrade",{Sodexo Deployment Queue Row Creation Month},@cell="June",{Sodexo Deployment Queue Year},@cell="2022"")

My attempt at putting everything together as I do not wish to create a helper column:

=COUNTIFS(OR({Deployment Queue Project Cancelled}, @cell="0",{Deployment Type},OR(@cell="New Deployment",@cell="Package Upgrade", {Sodexo Deployment Queue Row Creation Month},@cell="June",{Sodexo Deployment Queue Year},@cell="2022") , OR ( =COUNTIFS({Deployment Queue Project Complete},@cell="0",{Deployment Type,OR)@cell="New Deployment",@cell="Package Upgrade",{Sodexo Deployment Queue Row Creation Month},@cell="June",{Sodexo Deployment Queue Year},@cell="2022"")

Hoping this will be the last time I need your assistance today. I really appreciate your time and patience!!

Paul Newcome

My apologies. You literally add the two together.

=COUNTIFS(................) + COUNTIFS(...................)

deb_63_hydracor

Hi Paul,

I am still unable to get this formula to work "adding" the two statements together. Would you mind writing out (using copy and paste!) the complete formula for me so that I can make sure that parenthesis, commas, etc are all placed correctly?

Thanks!