Can't get a SUMIFS Excel formula to work

Question

Can't get a SUMIFS Excel formula to work

alan Sears 20

Is it possible to tell from this why the formula won't work? The values from each of the five lines seems OK to me, so I can't see what's up, but then I'm cross-eyed and punch drunk now!

Formula: =SUMIFS(Table3[Amount],[Column1],1,Table3[BudgetName],Budgets!C$1) gives #VALUE! 2026-08-23

0 comments

Answer accepted by question author

Marcin Policht 107.2K MVP Volunteer Moderator

Yep - the Function Arguments window actually reveals the problem - the ranges are not the same size. Excel's SUMIFS requires the Sum_range and every Criteria_range to have exactly the same number of rows.

In your screenshot:

Table3[Amount] → 5 values [Column1] → 12 values Table3[BudgetName] → 4 values

So Excel is trying to compare ranges of different sizes, which produces #VALUE!.

There is also a second thing you'd want to correct. Your formula has:

[Column1]

whereas the other structured references are explicitly qualified as Table3[...]. If Column1 is a column in Table3, it should be:

Table3[Column1]

So the formula should be:

=SUMIFS(Table3[Amount],Table3[Column1],1,Table3[BudgetName],Budgets!C$1)

However, that will only work if Table3[Amount], Table3[Column1], and Table3[BudgetName] all contain the same number of data rows.

The arrays displayed by Excel in the dialog should all have the same number of entries. Your next step should be to look at Table3 itself and make sure those three columns extend through exactly the same rows. If Amount has 5 rows but BudgetName has only 4, for example, that is the underlying problem.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

0 comments

2 additional answers

Your answer

Answer 1

#VALUE! with SUMIFS commonly means the range sizes are inconsistent.

In SUMIFS, sum_range and every criteria_range must have the same number of rows and columns. That is the first thing to check in this formula:

=SUMIFS(Table3[Amount],[Column1],1,Table3[BudgetName],Budgets!C$1)

The likely issue is [Column1]. In a table formula, structured references normally need to point to the same table/range as the other arguments. If [Column1] is not resolving to a range with exactly the same dimensions as Table3[Amount] and Table3[BudgetName], SUMIFS can return #VALUE!.

Use the fully qualified table reference instead:

=SUMIFS(Table3[Amount],Table3[Column1],1,Table3[BudgetName],Budgets!C$1)

Checks to make:

Table3[Amount], Table3[Column1], and Table3[BudgetName] must all cover the same rows.
Budgets!C$1 should contain the text exactly as it appears in Table3[BudgetName].
If the source data is in another workbook, SUMIF/SUMIFS can return #VALUE! when that workbook is closed. Open the referenced workbook and recalculate.

If the formula still fails, verify that Table3[Column1] is really the intended column and not a shorthand reference being interpreted differently in that cell context.

Answer 2

Hi!

Welcome to the Microsoft Q&A forum.

Based from your formula of =SUMIFS(Table3[Amount],[Column1],1,Table3[BudgetName],Budgets!C$1)

[Column1]. That mismatch produces #VALUE!.

Table3[Amount] → 5 values: {53;129;13;2.51;99}

[Column1] → 12 values: {1;2;3;4;5;6;7;8;9;10;11;12}

Table3[BudgetName] → 5 values: {"Dividend";"Food";"Food";"Food"...}

You've got 5 rows in the Table3 ranges but 12 rows in [Column1]. That mismatch produces #VALUE!

Can you try adding Table3 in front of [Column1]

It should look like this. =SUMIFS(Table3[Amount],Table3[Column1],1,Table3[BudgetName],Budgets!C$1)

Let me know what will happen