# 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().