Welcome Excellers to another #formulafriday. Today let’s look at a scenario which uses the COUNTIF Function in Excel to find the number of cells we have in one range that are greater than another range. The example I am going to use is from this data extract of online sales transactions for June 2017.
In this data extract I want to see how many sales online are over €10.00. This is ideally the minimum sales value we want for each transaction. Calculating how many sales in June met this threshold is easily done using the COUNTIF Function with Excel.
Let’s take a quick look at the Syntax of COUNTIF which is one of Excel’s statistical functions.
= COUNTIF(range, criteria)
where
range – this is the cells that you want to apply the criteria to
criteria – this is the condition that a cell content should meet to be counted
Now we understand that let’s build our formula.
First we specify our range, in this example it is B2:B22.
Then we create the criteria, we want to count all instances of sales equal to or over our €10.00 target, of which we have 17 that match this threshold.
Let’s also look at how many sales did not meet the €10.00 threshold, all we need to do is change the criteria to <€10.00.
We have three transactions in June 2017 below our ideal threshold. Notice that we surround our criteria with the quotations marks ” “. This is because we are not only looking for a number we are specifying >= greater than and < less than. We would not need the quotation marks if our criteria was simply a number -€10.00.
Over To You!
How would you calculate this using Excel?. Share in the comments below.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
