Countif w/multiple <>s

[gino59]

Hi all!!

Hoping someone can unclog my brain and help me with this one!

I have formulae that pull the top 3 values from a column (Table1[State]).
What I'm trying to get is a count of all the other values that DO NOT equal the value in cell C3, C4 or C5.

So, in cell B6, I've entered this formula...

=COUNTIF(Table1[State],(AND(Table1[State]<>$C$3,Table1[State]<>$C$4,Table1[State]<>$C$5)))

Basically count the entries in Table1[State] column that don't equal any of the values in C3, C4, or C5.

I'm sure this is a brain cramp of magnitude 9, but I can't figure it out!

Many thanks as always!!

Cheers,
Gino

 

[gino59]

Okay - if anyone's interested - I finally figured it out!

Using an array formula:

{=SUM(IF(Table1[Country]="United States",IF(Table1[State]<>$C$3,IF(Table1[State]<>$C$4,IF(Table1{State]<>$C$5,1,0),0))))}

Cell C3 has the most frequently listed state, followed by C4 with the 2nd most frequently counted state, and likewise for C5 (3rd most). Using this formula in cell C6, gives me a total count of all US states, excluding the top 3. Just what I wanted!

Hope this helps others!

Cheers!
Gino

 

[ChrisOswald]

Gino,

You probably could also just get a total count, and then subtract 3 countif's for the top three values.

=count(Table1[Country])-countif(Table1[Country],$C$3)-countif(Table1[Country],$C$4)-countif(Table1[Country],$C$5)

For my edification: what kind of thing does Table1[Country] refer to? It looks to be an invalid name for a range, at least in 2007.

 

[gino59]

Hey Chris!

Thanks for the response! The challenge was in counting the number of entries for all other US states that were not in the top 3. The rows without data for state (other countries) did pose a challenge too.

In Excel 2007, I have a table called Table1 and within that table is a column called Country. So, basically it was count the entries in the State column that don't equal any of the state values in cells C3, C4, & C5 and only count the state values if the Table1[Country] cell is equal to "United States". Needed that because otherwise it would try counting the blank state cells. Couldn't figure how to do a COUNTAIF!

Cheers!

Gino

 

[ChrisOswald]

...and I learned what a table is in Excel. Too bad most of my co-workers are still on 2003

 

[Matty]

Given you have Excel 2007, I would use COUNTIFS for this. Like:

=COUNTIFS(Table1[Country],"United  States",Table1[State],"<>"&C3,Table1[State],"<>"&C4,Table1[State],"<>"&C5)

Matty

 

[gino59]

Chris - I'm actually doing this with Excel 2010. The tables functionality in 07 (same as 10) is great! Lots less range naming for sure! Hopefully you can get your co-workers up to at least 2007...

Matty - excellent! Much easier than the array formula (and probably less expensive)! I knew COUNTIFS should have worked - it was definitely the syntax of "<>"& cell reference that did the trick!

Thanks to both of you!

Cheers!

Gino

 

[Matty]

Matty - excellent! Much easier than the array formula (and probably less expensive)! I knew COUNTIFS should have worked - it was definitely the syntax of "<>"& cell reference that did the trick!

Glad it worked. Array formulas definitely have their place, but if there's a native function that will do the trick, I'd always opt for it over an array equivalent...

Matty