Can you help with a counting formula question?

Jeffrey Metivier

Hi - I'm trying to create a count if formula that will include Bid Stage from one column and either of Open or On Pause criteria from another column. 

This is what I tried =COUNTIFS({2020.3.31 Antitrust MA Subgroup Chart Range 2},"Bid Stage", [{2020.3.31 Antitrust MA Subgroup Chart Range 1}, "Open", {2020.3.31 Antitrust MA Subgroup Chart Range 1}, "On Pause"])

I appreciate any help you can provide. Thank you!

adam.skinner107256

You're welcome. From the help on the CountIfs function: "Counts the number of cells within a range that meet all of the specified criteria."

https://help.smartsheet.com/function/countifs

You may be able to add an OR

https://help.smartsheet.com/function/or

Something like this should work:

=COUNTIFS({2020.3.31 Antitrust MA Subgroup Chart Range 2}, "Bid Stage", {2020.3.31 Antitrust MA Subgroup Chart Range 1}, OR(@cell = "Open", @cell = "On Pause"))

adam.skinner107256

Is the square bracket necessary on this? I think it shows in the help text, but looking at examples of =COUNTIFS I'm not using them. When I pasted this into a sheet it gave Unparsable, but when I removed the [ ], it seemed to work.

Jeffrey Metivier

Thank you, Adam. I removed the brackets and I get a result of 0. I know there are items that meet this criteria. Do you think the way I've structured the formula will result in only instances that contain Open AND On Pause, instead of the intended Open OR On Pause?

adam.skinner107256

You're welcome. From the help on the CountIfs function: "Counts the number of cells within a range that meet all of the specified criteria."

https://help.smartsheet.com/function/countifs

You may be able to add an OR

https://help.smartsheet.com/function/or

Something like this should work:

=COUNTIFS({2020.3.31 Antitrust MA Subgroup Chart Range 2}, "Bid Stage", {2020.3.31 Antitrust MA Subgroup Chart Range 1}, OR(@cell = "Open", @cell = "On Pause"))

Jeffrey Metivier

Adam -- thank you for your help!