COUNTIF AND COUNTIFS

I am doing a little bit of work with a former colleague and he asked me for my advice on the COUNTIF() function. Not a difficult problem but not everyone knows everything! He is working on a database and as part of his analysis he wants to count the number of instances of a value or values within a range: such as greater than zero and less than 1. He knew that he can use COUNTIF() for this but when he then expressed the result of that function as a fraction of the total number of values in his database, it gave him an answer of greater than 1! Clearly that’s not possible! There can’t be more than 100% of values in a sub range of a range!!

I set up a simple spreadsheet for my colleague and added COUNTIFS() as something he should consider too. For example, I suggested, maybe you want to know the values that are greater than, say, 2 but less than, say 4. I incorporated selecting 2 and 4 from a cell rather than hard coding, since that would give him greater flexibility in his analysis: just change 2 to x and 4 to y to see instant updates to his query. In the end I suggested this layout:

criteria	f	f/n	Function	greater than	less than
>1	83	0.83	countif			basic
>0 <1	17	0.17	countifs			basic
>2<4	42	0.42	countifs	2	4	using cell refs

This layout contains the following formulas, note the use of the “data” range name:

criteria	f	f/n	Function	greater than	less than
>1	=COUNTIF(data,”>1″)	=D6/COUNT(data)	countif			basic
>0 <1	=COUNTIFS(data,”>0″,data,”<1″)	=D7/COUNT(data)	countifs			basic
=”>”&G8&”<“&H8	=COUNTIFS(data,”>”&G8,data,”<“&H8)	=D8/COUNT(data)	countifs	2	4	using cell refs

The data start in cell A6 …

Notice, to use COUNTIFS() with criteria in a cell rather than hard coded in a formula, you need to do this:

“>”&G8 … the greater than sign must be enclosed in double inverted commas and you must then CONCATENATE the cell reference to that.

Download my Excel file, countif_countifs_eg and modify it as you wish. You should appreciate the difference between COUNTIF() and COUNTIFS() too.

RANDBETWEEN

In a comment in cell A5, I explain how and why I use the RANDBETWEN() function in this work book. I also say that although I almost always use the RANDBETWEEN() function to generate my random numbers, it is possible to use RAND() for that too, look for my page here, RANDBETWEEN using RAND().

As always, your comments are valuable to me.

Duncan Williamson