Countifs between values

[Dropout]

Hi,

I've been using this site as a reference for a long time, and there are plenty of helpful people out there

I have (hopefully) a simple query regarding countifs between values.

I know that I can obtain the relevant info I require by using the following statement:
=COUNTIFS($A:$A,">30",$A:$A,"<=60")

This will count all values in A:A where the value is greater than 30, and less than or equal to 60.

I came across the use of {} in conjuction with either a "," for OR or a ";" for AND.

So, I thought that this should do the same thing:
=COUNTIFS($A:$A,{">30";"<=60"})

As I'm here posting this, you guessed it, it doesn't work.
I'm curious as to why. If anyone knows I'd be interested to find out

Thanks in advance.

 

[Mackers]

Hi

Firstly, you need to wrap the COUNTIFS() in a SUM() to do that, otherwise the COUNTIFS() will only output the first value (i.e. relating to >30).

=SUM(COUNTIFS(A2:A1000,{">30","<=60"}))

Secondly, it's vitally important to understand what this is doing:

The first pass of the formula is going through A2:A1000 and counting the number of cells greater than 30.

The second pass is then going through and counting the number of cells less than or equal to 60.

The SUM() wrapping these two then adds the totals together.

That means a value between 30 and 60 is counting twice. A value less than or equal to 30 is counting once and a value greater than 60 is counting once. This is not the same as an OR() or AND() function.

Hope that helps

Mackers

Edit: further reading by someone much smarter than me can be found here: https://excelxor.com/2014/09/28/countifs-multiple-or-criteria-for-one-or-two-criteria_ranges/

 

[Dropout]

Hey Mackers,

I understand the function now, thank-you for your help.
It seems that making it shorter will not give me the output I need. But if you don't try you'll never know right.

Cheers for the quick response.

 

[Mackers]

No worries. COUNTIFS() and SUMIFS() are very efficient and compact for what they do. The only thing I would say is to experiment using SUMPRODUCT() instead, as it works when the target sheet isn't open and is also more flexible with this kind of thing.

Having said that, I use the curly braces method when it is on a single column and I'm checking specific text strings rather than mathematical checks. To be clearer, if I have a list of people and I want to count everyone whose title is Ms or Dr, I can use {"Ms","Dr"} in a similar fashion and it will function like an OR() check. It just so happens that using inequalities won't achieve that (a number can be both greater than 30 and less than or equal to 60; a title cannot both be "Ms" and "Dr" at the same time).