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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920