Today, we’ll see a little magic from the COUNTIF function – it can do the work of seven formulas in one! We’ll use it to check winning numbers in an imaginary lottery.
The COUNTIF function can check a range of cells, and see if it contains a specific value. For example, type your lucky number in cell D2, and use a COUNTIF formula to see if that number is found in the list of winning numbers in cells I2:N2.
=COUNTIF(I2:N2,D2)
The number, 26, is found once in the range I2:N2, so the COUNTIF result is 1. Your lucky number is a winner!
Check Multiple Numbers
What if we have multiple lucky numbers to check – not just one number? In the next screen shot, there are six numbers to check, entered in cells A2:F2. The winning numbers are still listed in cells I2:N2.
We could create a similar COUNTIF formula, to check each cell, to see if it is a winner. Here is the formula in B5 – the column references in I2:N2 are locked, so that reference won’t change when the formula is copied across to cell G5.
=COUNTIF($I2:$N2,D2)
In cell H5, use the SUM function to find the total number of cells that match the winning numbers.
=SUM(B5:G5)
That shows a total of 1, so only one of the six lucky numbers is a winner.
Get the Total Count in One Step
Instead of creating 6 COUNTIF formulas, and a SUM, we could use an array-entered COUNTIF formula, to get the answer in one step.
In the next screen shot, the following formula is array-entered in cell B5 – press Ctrl + Shift + Enter, instead of just pressing Enter.
=SUM(COUNTIF(B2:G2,I2:N2))
If you change the lucky number in cell E2 to 22, instead of 7, the number of winners changes to 2, instead of 1. I’ve added conditional formatting in cells B2:G2, to colour the winning numbers yellow.
With cells B2:G2 selected, the conditional formatting formula is the same as our earlier formula:
=COUNTIF($I2:$N2,B2)
Check Losing Teams
The same technique works with text too, and the range that’s being checked can be vertical, instead of horizontal.
In the next screen shot, 6 people have picked the teams that they think will win the four games being played today. When each game is finished, the losing team’s name is entered in column H.
A SUM/COUNTIF array formula calculates how many losing teams each person picked. So far, 3 games are finished, and it’s looking good for Al. He hasn’t picked any losing teams yet!
Here is the formula array-entered in cell F2 (Ctrl + Shift + Enter), and copied down to cell F8.
=SUM(COUNTIF(B2:E2,$H$2:$H$5))
Excel Dashboard Course Recommendation
For a limited time, Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course.
The course is video based, delivered online and is available 24/7. You’ll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.
I’ve been through this course, and highly recommend it. The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.
Click here to find out the details, read the student comments, and watch the ‘behind the scenes’ video that shows you what you’ll receive as a member.
__________________________
Great tip. I use this technique all the time, but with a variation. I wrap it all in SUMPRODUCT instead of SUM. I can avoid control+shift+enter that way.
Instead of {=SUM(COUNTIF(B2:G2,I2:N2))}
Try this =SUMPRODUCT(COUNTIF(B2:G2,I2:N2))
How about checking a years worth of past numbers to see how many times a numbers repeated it self not the entire range but taken each number from a grop of 6 number drawn.
Thank you