#SPILL! error with sumifs

[gcaporaso]

I am using office 365 and when I do a sumifs formula and I reference a entire column for my criteria I get either a zero or a #SPILL ! error. It only works when I reference the specific cell. This used to work in older excel versions. Can I no longer reference a full column for my Criteria?

for example:
Column A - 1
Column B - Dog a Cat in cells with one
D1 = dog
E1 = sumifs(A:A,B:B,D:D) this is an error, but if I change the Criteria to = sumifs(A:A,B:B,D1) it works.

 

[DRSteele]

Welcome to the forum.

Your formula creates 1.05 million answers. Notice that the only row in which it can reside is Row1; if you move it to Row2, the computer will bog down for a while and eventually provoke a #SPILL error.

I suspect you are getting a #SPILL error because there are other formulas or non-empty cells below E1.

Just what is your objective? You can't want 1.05 million sums.