Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 Nested Sumif's
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. "TKS_Mark" wrote... 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"),_C stG10) Again, you'd be better off asking these kinds of application-specific questions in application-specific newsgroups. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 Nested Sumif's
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. "TKS_Mark" wrote: 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. "TKS_Mark" wrote... 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"),_C stG10) Again, you'd be better off asking these kinds of application-specific questions in application-specific newsgroups. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 balks at 7 nested IFs | Excel Worksheet Functions | |||
Pasting a chart from Excel 2007 to Word 2007 trouble | Charts and Charting in Excel | |||
Exporting Quickbooks 2007 to Excel 2007 | Excel Worksheet Functions | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
Joining 2 SUMIF's ??? | Excel Discussion (Misc queries) |