Excel Formulas Made Simple: IF, COUNTIF, Conditional Formulas & Text Functions
Excel is one of those tools almost everyone has and almost nobody uses to its full potential. Most people get comfortable with sums and averages, then stop, leaving a huge amount of time-saving power on the table. The formulas that actually move the needle are the ones that let Excel make decisions: count things that match a rule, flag the rows that need attention, and clean up messy text automatically.
We put together a short series of videos walking through four of the most useful formula skills you can learn, and this guide pairs each video with a plain-English explanation so you can follow along, pause, and try it on your own spreadsheet. No accounting degree required, just a willingness to type a formula and see what happens.
Using IF Statements
What an IF statement does
An IF statement is the foundation of every “smart” spreadsheet. It asks a yes-or-no question about a cell and returns one answer when the answer is yes and a different answer when it’s no. The pattern is always the same: =IF(something is true, do this, otherwise do that). For example, =IF(B2>100, "Over budget", "OK") checks whether the value in B2 is greater than 100, and writes “Over budget” if it is or “OK” if it isn’t. Microsoft’s own IF function documentation covers the full syntax if you want to go deeper.
The reason IF is so powerful is that it turns a column of raw numbers into a column of plain-English status labels that anyone can read at a glance. Once you’re comfortable with a single IF, you can nest them, putting one IF inside another, to handle more than two outcomes, like grading scores into A, B, and C bands. Start simple, get one working, then build from there.
Using COUNTIF
Counting only what matters
COUNTIF answers a question you probably ask all the time: “How many of these match a rule?” Instead of counting every cell in a range, it counts only the ones that meet a condition you set. The pattern is =COUNTIF(range, criteria), so =COUNTIF(C2:C50, "Paid") tells you how many rows in that range say “Paid,” and =COUNTIF(D2:D50, ">500") counts how many values are greater than 500. For the complete syntax reference, see Microsoft’s COUNTIF function documentation.
This is the formula that replaces the slow, error-prone habit of filtering a list and reading the row count at the bottom of the screen. Because COUNTIF updates automatically as your data changes, you can build a little summary panel, open tickets, overdue invoices, items in stock, that’s always current without any manual recounting. It’s one of the fastest ways to turn a long list into a useful dashboard.
Conditional Formulas
Math that respects your rules
Conditional formulas are the family of functions that do math only on the rows that qualify. The two workhorses are SUMIF and AVERAGEIF, and they read just like COUNTIF: =SUMIF(range, criteria, sum_range). So =SUMIF(A2:A100, "Houston", B2:B100) adds up the values in column B, but only for the rows where column A says “Houston.” That’s how you total sales by region, expenses by category, or hours by project without sorting your data first.
When you need more than one condition at a time, the plural versions, SUMIFS and COUNTIFS, let you stack rules together, like “sum the revenue where the region is Houston and the month is September.” These conditional formulas are where a spreadsheet stops being a storage bin and starts being a real reporting tool, because you can slice the same dataset a dozen different ways without ever touching the underlying rows. Microsoft’s SUMIF and SUMIFS documentation covers every argument and edge case.
Text Functions
Cleaning up messy data automatically
Real-world spreadsheets are rarely tidy. Names come in as “SMITH, JOHN,” phone numbers have stray spaces, and someone always pastes a column with the wrong capitalization. Text functions fix all of that without retyping a single cell. The most useful ones are LEFT, RIGHT, and MID (to pull out part of a value), TRIM (to strip extra spaces), UPPER/LOWER/PROPER (to fix capitalization), and CONCAT or the & symbol (to stitch pieces together).
For example, =PROPER(TRIM(A2)) takes a messy ” jOHN smith ” and cleanly returns “John Smith.” Combine these functions and you can split a full name into first and last columns, rebuild a formatted address, or standardize an entire imported list in seconds. Once you’ve set up a text-cleaning column, you can copy it down thousands of rows and let Excel do the tedious work you’d otherwise be doing by hand. Microsoft’s TRIM function documentation has more detail on how it handles spacing edge cases.
Don’t Want to Memorize the Syntax? Let AI Write It
Here’s the part that changes everything for most people: you no longer have to remember the exact syntax. Modern AI assistants, Microsoft Copilot built into Microsoft 365, ChatGPT, and Claude, can write any of these formulas from a plain-English description. Tell it “write an Excel formula that counts how many invoices in column C are marked Overdue,” paste it into your sheet, and you’re done. You can even describe a messy column and ask it for the text functions to clean it up.
The skill that actually matters now is knowing what’s possible, that Excel can count by condition, sum by rule, and reshape text, so you know what to ask for. The formulas in this guide are exactly the vocabulary that helps you write better prompts. To get more out of these tools, grab our Claude AI cheat sheet and our ChatGPT cheat sheet for prompts you can copy and adapt.
Frequently Asked Questions
What’s the difference between IF and COUNTIF?
An IF statement evaluates a single cell and returns a result based on whether a condition is true or false, it’s about making a decision for each row. COUNTIF looks across a whole range and tells you how many cells meet a condition. In short, IF labels individual rows, while COUNTIF tallies up matches across many rows.
Do I need a newer version of Excel for these formulas?
No. IF, COUNTIF, SUMIF, and the common text functions have been part of Excel for many years and work in virtually every version, including Excel on the web and the Microsoft 365 desktop apps. Newer versions add conveniences, but everything in this guide works on the Excel you almost certainly already have.
Can AI really write Excel formulas for me?
Yes. Microsoft Copilot, ChatGPT, and Claude can all turn a plain-English request into a working Excel formula, and Copilot can even build it directly inside your spreadsheet. The trick is describing what you want clearly, which gets much easier once you understand the basic building blocks covered in these videos.
Where can I find more Braintek how-to videos?
We regularly publish short, practical tutorials on Microsoft 365, Excel, and everyday business technology. These four videos are part of an ongoing series, and the cheat sheets linked above give you ready-made prompts to pair with them.
Want AI and Automation Working Inside Your Business?
Learning a few formulas is a great start, but the real wins come when AI and automation are wired into the way your whole team works, from spreadsheets to email to the systems that run your day. That’s the kind of thing we help Houston businesses set up so the technology quietly does the busywork for you.
If that sounds like something you’d like running inside your business, let’s talk. Book a free discovery call or call us in Houston at 281-367-8253.