Excel 2007 Nested Sumif's

[Guest]

I followed the instructions below and they work. But now, I have to wait 5
seconds after each new workbook entry before the cursor even moves. For some
reason, this is a memory hog. Is there something I can do about this? The
functionality is great, but it takes too long.

I want to sum columns of data only if two neighboring columns match two
separate criteria. For instance, I'm in worksheet, "wksht1" at column C.
I named column A "ActCdeSum". In another worksheet, I have three named
columns, "Area10", "ActCdeG10", and "_CstG10".

I want to sum the contents of "_CstG10" only if x "ActCdeG10" matches
"ActCdeSum" and "Area10" is has the text GB.

.....

So you want multiple cell ActCdeG10 to match item-by-item against multiple
cell ActCdeSum? If so, you can't use SUMIFS. You'd need to use SUMPRODUCT.

=SUMPRODUCT((ActCdeG10=ActCdeSum)*(Area10="GB"),_CstG10)

Again, you'd be better off asking these kinds of application-specific
questions in application-specific newsgroups.

 

[Guest]

to speed it up you might use a helper column with an if statement to see if
the the critereia is met for that row, and use a sumif() based on the helper
column.

sumproduct() is a very useful tool, but can be a memory and time hog, if
you have large arrays.