Forum Discussion

Jessica Marion's avatar
Jessica Marion
Copper Contributor
Apr 06, 2017
Solved

COUNTIFS and exclude a value

Hi,

 

I've been working on this all day and getting nowhere. I'm really hoping somebody can help me.

 

I've set up a summary sheet where I want to count how many of each value (1, 2 or 3) is in the Tier column, but only if the value in the Quarter column is not "Last FY". 

 

This is the formula I came up with, and Excel accepts it (no error messages), but no matter how I manipulate the values, the result of the formula is always "0".

 

=COUNTIFS(ESPA[Tier],1,ESPA[Quarter],"< > Last FY")

 

The formula was working fine until I added the exclusion, and I should mention that both the Quarter and the Tier columns have had data validation applied so that the user selects the value from a drop down menu.

 

I appreciate any input!

 

-Jessica

4 Replies

    • Jessica Marion's avatar
      Jessica Marion
      Copper Contributor

      Thanks for responding.

       

      I've tried that, and when I do, it just counts all the Tier 1's whether or not the Quarter column says "Last FY".

Resources