Excel - How to Use COUNTIF in Excel for Variable Upper Limit - Episode 1175

455 views · Published 2 February 2010 · 2:07 · Indexed 28 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Countif all records below a variable range.

Welcome back to the MrExcel netcast! I'm Bill Jelen and I want to thank all of you for your patience as I work on finishing five books about Excel 2010 in the final five weeks. But don't worry, I'm still committed to bringing you valuable content and today's podcast is all about using the COUNTIF function.

This question came in from Trinidad, where I did a seminar a few years ago and may be going back in June. So if you're looking for a great place to attend an Excel seminar, consider Trinidad! Now, onto the question at hand. Someone was trying to use COUNTIF to sort a variable range, but their formula wasn't quite working. They were using COUNTIF and trying to look at all these cells to see if it's “<&A2”. 

But here's the thing, they were so close! All they needed to do was concatenate two things - the less than sign, which needs to be in quotes, and then the cell reference A2. This way, when Excel evaluates the formula, it takes the number in A2 and combines it with the < sign, making it work perfectly. And as you can see, when we copy the formula down, it works for all the months in column A. 

If you're still a bit confused, let's take a look at the Evaluate Formula feature. As you can see, the first thing that happens is A2 gets evaluated, giving us the number 0.45. Then, that number gets combined with the < sign, giving us the criteria range we need for the COUNTIF function. And there you have it! I hope this tip helps you in your Excel journey. Thanks for stopping by and don't forget to tune in for more valuable content from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction and Sponsorship
(00:12) Question from Trinidad about using COUNTIF
(01:06) Explanation and solution for using COUNTIF with variable range
(01:35) Demonstration of solution using Evaluate Formula
(01:50) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Concatenation in Excel
COUNTIF formula
Criteria range in COUNTIF
Dueling Excel podcasts
Evaluate Formula feature
Netcast from MrExcel
Sorting variable range
Trinidad Excel seminar
Using  and & in formulas


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152785/

Use countif for variable value.

A question from Trinidad about using countif. Episode #1175 shows you how.

More from this channel