In this article 6 sections
- 1. Review the formula shape that causes the error
- 2. Count the rows in each range
- 3. Match the sum range to the criteria ranges
- 4. Distinguish the rules for SUMIF and SUMIFS
- 5. Pre-submission checklist
- 6. Verify the result
When a SUMIFS formula returns a #VALUE! error, first check whether all ranges in the formula have the same number of rows and columns. Microsoft Support explains that in SUMIFS, the sum range (sum_range) and criteria ranges (criteria_range) must have the same number of rows and columns. This article organizes the steps for checking them. It is based on examples in official documentation, not on measurements taken from an actual worksheet.
1. Review the formula shape that causes the error
In an example from Microsoft Support, the sum range C2:C10 has 9 rows, while the criteria ranges A2:A12 and B2:B12 each have 11 rows. The documentation states that entering =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14) in this state produces a #VALUE! error. Microsoft Support: How to correct a #VALUE! error in the SUMIF/SUMIFS function ↗
2. Count the rows in each range
Write down each range address used in the formula and calculate its number of rows. To count rows, subtract the starting row from the ending row and add 1. C2:C10 is 10 − 2 + 1 = 9 rows, and A2:A12 is 12 − 2 + 1 = 11 rows. If a range spans multiple columns, count and match the columns in the same way. If even one row count differs, the quickest approach is to fix the sum range first.
3. Match the sum range to the criteria ranges
The solution in Microsoft's documentation is to change the sum range to C2:C12 and recalculate the formula. Once the row count is 11, all three ranges are the same size. If rows are added to the sheet every day, you can also consider using the Table feature instead of fixed addresses, but regardless of the method, you must check again that the sum range and criteria ranges are the same size.
4. Distinguish the rules for SUMIF and SUMIFS
Microsoft's documentation states that the similar SUMIF function can use ranges of different sizes. SUMIFS, however, requires the ranges to be the same size. The argument order is also different. In SUMIFS, the sum range (sum_range) is the first argument, while in SUMIF it is the third argument. This order is often changed when copying and editing a function, so check it as well. Microsoft Support: SUMIFS function ↗
5. Pre-submission checklist
① Do the sum range and all criteria ranges have the same starting and ending rows? ② If you used column ranges, do they also have the same number of columns? ③ Does any criteria string exceed 255 characters? Microsoft explains that SUMIF and SUMIFS may return incorrect results with strings longer than 255 characters, and advises shortening them or splitting them with the & operator. ④ If you are referencing a range in another file, did you open that file and recalculate with F9? The same document also states that referencing a closed workbook can cause a #VALUE! error.
6. Verify the result
After fixing the formula, create a small range with one or two criteria and compare the formula result with a value calculated by hand. For example, if the same item and region appear in two rows, add only the values in those two rows and check whether the result matches the formula. This verification helps you quickly find cases where range addresses are offset by one row.
Related articles: How to anonymize example rows when asking AI about Excel formulas ↗, How to create an Excel drop-down list and configure settings to reduce input errors ↗
Sources: Microsoft Support, 'How to correct a #VALUE! error in the SUMIF/SUMIFS function' and 'SUMIFS function' (English). Both pages were opened and read on Oct 29th 2026 KST.
Sources and original article
Read the Korean original on Naver ↗
This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.
This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy