How to Write an Excel Formula with 5 or More Criteria

A user on the Microsoft Community forum recently posted a common frustration: “I’m trying to calculate total sales, but only if the Region is ‘West’, the Category is ‘Tech’, the Sales Rep is ‘John’, the Month is ‘March’, and the Sale Amount is over $500. I can’t figure out how to string 5 different criteria together without getting an error.”

When you need to perform calculations based on multiple conditions (AND logic), Excel provides dedicated “S” functions like SUMIFS, COUNTIFS, and AVERAGEIFS.

The “Quick Fix” Formula

If you need to sum values based on 5 criteria, the syntax follows a predictable pattern. Here is the formula template:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3, criteria_range4, criteria4, criteria_range5, criteria5)

Tested on: Excel 365, Excel 2021, and Excel for the Web as of 2024.

---

Step-by-Step Breakdown

Let’s look at how to build this using the user’s specific data. Assume your data sits in columns A through E, and the values to sum are in column F.

Argument	Description	Example Value
Sum_Range	The actual cells to add up.	F:F (Sales Amount)
Criteria_Range1	The first range to evaluate.	A:A (Region)
Criteria1	The condition for Range 1.	"West"
Criteria_Range2	The second range to evaluate.	B:B (Category)
Criteria2	The condition for Range 2.	"Tech"
Criteria_Range3	The third range to evaluate.	C:C (Rep)
Criteria3	The condition for Range 3.	"John"

The Completed Formula: =SUMIFS(F:F, A:A, "West", B:B, "Tech", C:C, "John", D:D, "March", E:E, ">500")

Why This Works

1. AND Logic: The SUMIFS function is designed so that a row is only included in the total if all criteria are true at the same time.
2. Range Pairing: Every criteria must have a corresponding range. You cannot list one range and then five criteria; you must repeat the range for every test.
3. Quotes for Text and Operators: Notice that “West” and “>500” are in double quotes. In Excel formulas, text strings and logical operators (like greater than/less than) must be wrapped in quotes.

---

Common Traps to Avoid

• Mismatched Range Sizes: This is the #1 cause of the #VALUE! error. If your Sum_Range is F2:F100, all your Criteria_Ranges must also be exactly row 2 to row 100. Using full columns (e.g., F:F) for everything is the easiest way to avoid this.
• The “OR” Confusion: SUMIFS only works if Criteria 1 AND Criteria 2 are true. If you need to sum if the Region is “West” OR “East”, you generally need to add two SUMIFS together: =SUMIFS(...) + SUMIFS(...).
• Circular References: Ensure your formula is not placed inside one of the columns it is calculating (e.g., putting a formula that sums Column F into cell F500).

---

Related Questions

What if I want to count rows instead of summing them?

You would use the COUNTIFS function. The logic is identical, but you omit the “Sum_Range” at the beginning. Example: =COUNTIFS(A:A, "West", B:B, "Tech", C:C, "John", D:D, "March", E:E, ">500")

Can I use cell references instead of typing “West”?

Yes, and this is highly recommended for building dynamic dashboards. If you type “West” in cell H1, your formula becomes: =SUMIFS(F:F, A:A, H1, ...). This allows you to change the criteria by simply typing a new value in cell H1 without editing the formula.

How do I handle dates as criteria?

Dates can be tricky. If you are looking for sales after January 1st, 2023, use the DATE function inside your criteria to avoid regional formatting errors: =SUMIFS(F:F, G:G, ">"&DATE(2023,1,1)) Note the use of the ampersand (&) to join the operator to the date function.