Excel Sum Based on Another Column: SUMIF and SUMIFS Formula Examples 2026

Learning how to do an Excel sum based on another column is one of the fastest ways to turn a messy list into clear answers. Imagine you are planning a trip and comparing Excellence Playa Mujeres against two other adults-only options. Your spreadsheet holds hundreds of rows of costs, and you only want the total for one resort. Instead of filtering and adding by hand, a single formula reads one column, checks a condition, and adds the matching numbers from a second column automatically.

The function that does this is SUMIF, and a good excel sumif formula example shows its logic in one line. SUMIF takes three arguments: the range to test, the criteria to match, and the range to add. For instance, =SUMIF(A2:A200,"Excellence Playa Mujeres",C2:C200) tests every cell in column A, and whenever it finds that resort name it adds the number from the same row of column C. The result updates instantly whenever your data changes.

Why does this matter beyond vacation planning? Almost every real spreadsheet has one column that describes something and another that measures it. Sales by region, hours by employee, expenses by category, and scores by student all follow the same pattern. Once you understand conditional summing, you stop copying numbers into calculators and start building reports that refresh themselves. Employers frequently test this skill, and it appears on nearly every Excel certification practice exam, from beginner to advanced levels.

This guide walks through the full toolkit. You will start with plain SUMIF, then move to SUMIFS for several conditions, then see how wildcards, comparison operators, and dates extend both functions. We also compare alternatives such as SUMPRODUCT, pivot tables, and the lookup approach behind VLOOKUP in Excel, so you can pick the right tool for each job. Every example uses a realistic trip budget, which keeps the numbers concrete and easy to verify with your own data.

You do not need advanced skills to follow along. If you can click a cell, type an equals sign, and select a range, you can build every formula here. Along the way you will also see housekeeping tricks that make a summary sheet cleaner, such as how to create a drop down list in Excel for the criteria cell, how to freeze a row so headers stay visible, and when merging cells helps or hurts your formulas.

By the end, you should be able to sum any column based on text, number, or date conditions, diagnose the usual errors, and explain your formula to a colleague. Practice matters more than reading, so build the sample table yourself, type each formula, and compare your totals with the expected results. Then test yourself with the free practice questions linked below to confirm that the syntax is firmly in your memory before an exam or a real job task.

SUMIF Syntax Explained With a Resort Budget Example

Start with a simple table. Column A lists the resort, column B lists the expense type, and column C lists the cost in US dollars. Rows include Excellence Playa Mujeres at $2,850 for a week, Excellence El Carmen at $2,640, Excellence Coral Playa Mujeres at $3,120, and several smaller expenses for each property. The goal is a summary that shows the total spent per resort without sorting or filtering the original data.

The syntax is =SUMIF(range, criteria, [sum_range]). The range is the column Excel inspects, such as A2:A100. The criteria is what must match, written as text in quotes, a number, an expression like ">500", or a cell reference. The sum_range is the column that gets added, such as C2:C100. If you omit sum_range, Excel adds the cells in the first range itself, which works only when that range holds numbers.

Always keep the range and sum_range the same size. If A2:A100 is tested but C2:C150 is summed, Excel resizes the sum range from its top-left cell, which can quietly produce wrong answers. Using whole-column references like A:A and C:C avoids a mismatch and automatically includes new rows, though very large workbooks may calculate slightly slower. Many professionals instead convert the data to an Excel Table so references expand with the data and stay aligned.

Putting the criteria in a cell makes the formula reusable. Suppose E2 contains Excellence Coral Playa Mujeres. The formula =SUMIF($A$2:$A$100,E2,$C$2:$C$100) returns that resort's total, and dragging it down a list of resort names fills the whole summary. The dollar signs lock the ranges so they do not shift as you copy. This single habit, referencing a criteria cell, separates a quick calculation from a reliable report you can hand to someone else.

You can make that criteria cell safer with a dropdown. Select E2, open Data Validation, choose List, and point it at your resort names. Anyone using the sheet can then pick a resort instead of typing it, which eliminates the misspellings that cause zero totals. This is the standard answer to how to create a drop down list in Excel, and it pairs naturally with SUMIF because the formula recalculates the instant the selection changes.

SUMIF is also not case sensitive, so Excellence Resorts and EXCELLENCE RESORTS match the same rows. It does treat extra spaces as part of the text, though, which is a common trap when data is pasted from websites. If a total looks too low, check for trailing spaces with the LEN function or clean the column using TRIM. Fixing data quality first usually resolves a problem more quickly than rewriting the formula itself.

Common SUMIF Errors and How to Fix Them

The most common complaint is that SUMIF returns zero even though matching rows clearly exist. In most cases the numbers are stored as text. You can spot them by the small green triangle in the corner of the cell or by left alignment. Select the column, click the warning icon, and choose Convert to Number. Alternatively, multiply the column by one with Paste Special. Once the values are true numbers, the formula totals them correctly.

A #VALUE! error usually has one of three causes. The criteria string may exceed 255 characters, the formula may reference a closed external workbook, or a SUMIFS range may be a different size from the others. SUMIFS is strict here: every range must have identical rows and columns. SUMIF is more forgiving but still unreliable when sizes differ. Compare the row numbers in each range, and the error normally disappears right away.

Wrong totals without any error message are more dangerous. They typically come from partial ranges, such as summing A2:A90 when data extends to row 120, or from criteria typed with a typo. A quick audit method is to filter the source column for your criteria and read the total in the status bar at the bottom of the window. If that number differs from your formula result, the formula needs attention.

Hidden characters cause many mysterious mismatches. Data copied from web pages often contains non-breaking spaces that TRIM does not remove. Use =SUBSTITUTE(A2,CHAR(160),"") to strip them, or wrap CLEAN around the cell to remove non-printing characters. Create a helper column with the cleaned text, then point SUMIF at that helper column. It is a small extra step, but it saves hours of hunting through a long list for the culprit.

Merged cells deserve a warning. When you learn how to merge cells in Excel, it feels handy for report titles, but merging inside a data table breaks conditional sums. Only the top-left cell of a merged block holds a value, so the other rows look blank to SUMIF and get skipped. Unmerge the range, fill the values down, and consider Center Across Selection for visual alignment instead, which keeps every cell intact.

Date conditions are another trap. If dates are typed as text, comparisons such as ">="&G1 fail silently. Use DATEVALUE to convert them, or re-enter them in a recognized format. When auditing long sheets, learn how to freeze a row so your header stays visible while you scroll: choose View, Freeze Panes, then Freeze Top Row. Seeing the header while you check criteria prevents the classic mistake of summing the wrong column.

Real-World Examples: Budgets, Sales Reports, and Study Trackers

Consider a monthly travel fund. Column A holds the resort, column B the month, and column C the cost. A manager wants to know how much was spent at Excellence El Carmen during March. The formula =SUMIFS(C:C,A:A,"Excellence El Carmen",B:B,"March") answers in one cell. Duplicate it for each resort and month, and you have a grid that works as a full budget dashboard, refreshing the moment a new expense row is added.

Sales reports follow the same pattern. A table might list salesperson, region, and revenue. To total revenue for the West region, use SUMIF with the region column as the range. To find West region revenue from sales above $1,000, switch to SUMIFS and add a second condition on the revenue column. Teams commonly place these formulas on a summary tab that managers open every Monday morning to review results.

Learners can use the same idea for study tracking. Suppose you log hours by topic while preparing for a certification at an institute of creative excellence or any other training provider. Column A holds the topic, column B the hours. =SUMIF(A:A,"Formulas",B:B) shows how long you have spent on formulas. Comparing topic totals reveals neglected areas, and you can rebalance your schedule before test day using real data.

Personal finance works nicely too. Track books purchased for self-development, such as an inner excellence book, alongside gym fees and online courses, and tag each with a category like Learning. A single SUMIF reveals your annual learning spend. Add a second condition for the year and you can compare 2025 against 2026. Seeing categories as numbers often changes habits more effectively than a vague feeling about where the money went.

For team projects, use SUMIF to total hours by assignee or task status. If you maintain a project tracker, sum the budget for tasks marked In Progress, then separately for Completed. This gives a live picture of committed versus delivered cost. Combined with conditional formatting, a red cell can appear when a category exceeds its budget. Stakeholders do not need to understand the formula; they simply see an accurate and current number.

When results matter, protect the summary sheet. Place formulas on one tab and raw data on another, lock the formula cells, and let colleagues edit only the input tab. Document each formula with a short note beside it explaining the criteria. Six months later, you or a coworker will be glad the logic is written down, and audits of the workbook become far less stressful for everyone involved in the process.

Practical Tips to Master Conditional Sums Before Your Exam

Practice beats memorization. Build a small table of at least 30 rows with a text column and a number column, then write ten different SUMIF formulas against it: exact match, wildcard match, greater than, less than, not equal, blank cells, and cell-referenced criteria. Check each result by filtering manually. Doing this once cements the argument order far better than rereading notes, and it reveals your personal weak spots quickly so you can target them.

Memorize the argument order difference. SUMIF puts the sum_range last and makes it optional, while SUMIFS puts the sum_range first and requires it. Exams love this distinction, and so do real workplaces, where swapped arguments return plausible but wrong numbers. A simple memory aid: in SUMIFS, the S stands for sum first. Say it aloud while typing until the habit becomes automatic and you no longer need to stop and think.

Use keyboard shortcuts to speed up. Press F4 to cycle through absolute and relative references, Ctrl+Shift+Down to select a column of data, and Alt+= to insert an AutoSum. Pressing F2 on a cell enters edit mode and colors each range, which helps you verify that the ranges line up. Faster editing means more repetitions in the same study session, and repetitions are what build lasting fluency with formulas.

Learn to read formulas, not just write them. Exam questions often show a formula and ask what it returns, or show a wrong result and ask for the fix. Practice by tracing a formula row by row: which rows meet the criteria, which values get added, and what the total is. Use the Evaluate Formula tool on the Formulas tab to step through calculations and see each intermediate result.

Pair SUMIF with its siblings. COUNTIF counts matching rows, AVERAGEIF averages them, and MAXIFS and MINIFS return the largest and smallest matching values. They share nearly the same syntax, so learning one teaches the rest. When you are comfortable, combine them: total spent divided by number of transactions gives an average, and you can check it against AVERAGEIF to confirm your logic is correct before relying on it.

Finally, schedule short, consistent sessions. Twenty minutes a day for two weeks outperforms one four-hour cram session. Alternate between building formulas on your own data and answering timed multiple-choice questions. Review every wrong answer until you can explain it. Keep a personal cheat sheet with your most useful patterns, and when a new task arrives at work, apply the formulas to real data immediately so the skill sticks.