Help

EMueller

This is my code with a picture of my smartsheets filter as well:

=COUNTIFS({GDP Project Tracker Range 5}, "<0.05", {GDP Project Tracker Range 2}, "Portable", {GDP Project Tracker Range 6}, YEAR(@cell) = 2024, {GDP Project Tracker Range 3}, OR(@cell = "Active", @cell = "Launched", @cell = "Planned")) + COUNTIFS({GDP Project Tracker Range 5}, "<0.05", {GDP Project Tracker Range 2}, "Portable", {GDP Project Tracker Range 7}, YEAR(@cell) = 2024, {GDP Project Tracker Range 3}, OR(@cell = "Active", @cell = "Launched", @cell = "Planned"))

I need the code to result how many rows all of the following criteria match up with.

1. Look for it to be a portable
2. look for it to be less then 5%
3. need it to be within the year 2024
4. need it to be an active, planned, or launched project

Unfortunately, I should be getting a value of 3 where using this I get a value of 6. Not sure if there is something wrong or what it is! Any help would be appreciated!

Paul Newcome

You are adding the same COUNTIFS to itself. Remove one of the COUNTIFS and you should be fine.

EMueller

@Paul Newcome Is this still the same case if the M3 Baseline or the M3 Current has to be within the year 2024?

Paul Newcome

Sorry. I missed that you had two different ranges for the YEAR piece.

To replicate your filter, you would include both range/criteria sets within the same COUNTIFS. What you are doing is counting them separately and adding them together.

EMueller

@Paul Newcome You would only put them in one COUNTIFS even when the year statement is an OR statement?

Paul Newcome

The year piece is not an OR in your filter. If it is an OR statement then yes, you would add two separate ones together, but that is not how you have your filter set up.

EMueller

@Paul Newcome So then the origional code in the question would be the correct code. I am still getting double my answer with that code yet

Paul Newcome

Try writing out a third COUNTIFS that has both year columns being evaluated (similar to your filter) and subtract that from your existing formula.

=COUNTIFS(first year column) + COUNTIFS(second year column) - COUNTIFS(both year columns)