Selective Count

william.fx.bevilacqua14886

I’m trying to get a count based on 3 different conditions. The formula that I have so is

=countifs($[it track]$2:$status$500, “01. Reporting”, $[it track]$2:$status$500, “ Tier 1”, $[it track]$2:$status$500, “Green”) 

If all 3 conditions are met than a count is added. But its not working what am i missing?

Thanks

Mike Wilday

Try putting the dollar sign just in front of the row number. Remove it from the column names. 

=countifs([it track]$2:status$500, “01. Reporting”, [it track]$2:status$500, “ Tier 1”, [it track]$2:status$500, “Green”)

Not sure if that is going to work for your use case but based on how the absolute references work this is the correct syntax. 

https://www.smartsheet.com/blog/relative-and-absolute-references

Robert S.

Hello,

 

Thanks for the question. For the COUNTIFS function, the ranges for each criteria should be set to a single column wide. In your formula the ranges are all the same, and all range multiple columns. Here's an example of how this formula can be written:

=COUNTIFS([it track]2:[it track]500, "01. Reporting", [tier column]2:[tier column]500, "Tier 1", status2:status500, "Green")

 

To make this work for you, replace "tier column" with the name of the column that the "Tier 1" criteria is in. The $ symbol is used to create absolute references, and may not needed in this case. To learn more about absolute references and find out it you need to have them in this formula or not, check out the "Create an Absolute Reference" section of this help center article here (https://help.smartsheet.com/articles/2476171#reference), or check out the blog post that Mike linked to here (https://www.smartsheet.com/blog/relative-and-absolute-references).