Can't handle named ranges in SUMIFS

[dotsent]

Hi!

I'd need some ideas in troubleshooting SUMIFS formula that returns #VALUE ! error
I have a named ranges (within a table) to check particular "Department" + named range to check particular "Cost_type" and summarize the match. So the formula looks something like:

=SUMIFS(Sums,Cost_type,"Telephone",Department,"Sales")

I have a table with close to 2000 rows, however just a handful match the SUMIFS criteria, so it shouldn't be the string limitation. Some weird behaviour:


1) If I swap the named ranges to actual ranges (e.g. B2:B2066 for sums etc), it will work - so I don't think it can be a string limitation

2) If I use the same named ranges in plain SUMIF formula, it will handle those ranges just fine, e.g:

=SUMIF(Department,"Sales",Sums)

Any idea what might be causing this? What could be wrong with my ranges? Tried to re-create them, but to no avail. Thanks!

 

[Marcelo Branco]

Usually SUMIFS return a #VALUE ! error when the ranges do not have the same size.

To check the size of each range try in empty cells
=ROWS(Sums)
=ROWS(Cost_type)
=ROWS(Department)

M.

 

[kweaver]

I put in some fake data w/the same named ranges and it works ok. Can't replicate the error. Maybe the names are incorrectly entered?

 

[dotsent]

Had to expand and populate the table with data once more and that fixed it. Still not sure why SUMIF worked, but SUMIFS didn't but at least it is functional now. Thanks for all the contributions!

 

[Joe4]

Still not sure why SUMIF worked, but SUMIFS didn't but at least it is functional now

I am guessing maybe your ranges either were not all the same size, as Marcelo mentioned. That is a common cause of that particular error in a SUMIFS statement.