COUNTIFS question

Murz

EDIT - I think I solved my original problem by removing the <> before the "2099". However, how do I write the formula to show not equal to 2099 without using an operator? SS doesn't seem to like it when I use <>.

******

Hi, I've browsed a number of the COUNTIFS posts but for some reason, I can't get my formula to work. It used to work but stopped when I populated my reference sheet with data. Not sure how that would make a difference but its the only correlation I could find.

Its basic. Just counting all the project health statuses of "red" "yellow" and "green" by "in progress" that don't have an approved year of "2099". Its resolving but not counting the data in the referenced sheet.

=COUNTIFS({Project Health}, [Project Health]@row = "Red", {Project Status}, [Project Status]@row = "In Progress", {Approved Year}, <>"2099")

The only difference between the 3 formulas is the color.

Thank you in advance!

Kerry St. Thomas

There are a few things that I wonder might be contributing to the issue. One is the quotes around the 2099 - that's a numerical evaluation, so I wonder if you're excluding what is a number or if you're forcing a conversion to text with that. The more likely issue I see is you're trying to aggregate using @row. While it's possible, I find it to be a bit… well, temperamental. :) Perhaps you can use a combination of COUNT and COLLECT?

As an aside, is there a possibility that "Project Health" will ever be BLANK? If so, you might be undercounting something - so the solution would be fairly straightforward, like changing the column you're counting to something that will never be blank - I like using Autonumber columns for formulas like these.

Good luck!

Paul Newcome

Try replacing each of the [Column Name]@row pieces with @cell.

COUNTIFS({Project Health}, @cell = "Red", …………………………

Murz

Paul - it worked. Thank you!