SOLVED

# COUNTIFS and exclude a value

Copper Contributor

# 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

# Re: COUNTIFS and exclude a value

Hello Jessica

Remove the space between < and >.

# Re: COUNTIFS and exclude a value

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".

best response confirmed by Jessica Marion (Copper Contributor)
Solution

# Re: COUNTIFS and exclude a value

And space before "Last FY"

# Re: COUNTIFS and exclude a value

Oh my gosh that's it.  I thought I tried every combination of spaces vs no spaces, but I guess not!

Thanks so much for ending my agony :)

-Jessica

1 best response

Accepted Solutions
best response confirmed by Jessica Marion (Copper Contributor)
Solution

# Re: COUNTIFS and exclude a value

And space before "Last FY"