Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
follow up to nested subtotal/sumproduct
Hi again.
Yestyerday I was talking to Peo Sjoblom about a nested sumproduct with a subtotal. We did get it to work, but after I posted it was solved, I'd done some more testing, and realized that it was working in the reverse direction I'd wanted. i.e., =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A197,Summary!C10:C77,0))),--(ISNUMBER(MATCH(F4:F197&"",Summary!A10:A77&"",0))) ,--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A197)-MIN(ROW(A4:A197)),,))),(C4:C197)) where Summary is my sheet name for my secondary sheet. What I'm seeking to do is to filter a different worksheet (WkShtA), and then have my subtotal function on WkShtA look at my Summary sheet's contents and show the subtotal for the matching elements that I've filtered on my WkShtA, without filtering Summary. After some initial ponderings, I thought a nested subtotal within Sumproduct, or vice-versa would solve it. I tried this: =SUMPRODUCT(--(ISNUMBER(MATCH(Summary!C10:C77,A4:A197,0))),--(ISNUMBER(MATCH(Summary!A10:A77&"",F4:F197&"",0))) ,--(SUBTOTAL(3,OFFSET(Summary!$C$10,ROW(Summary!C10:C 77)-MIN(ROW(Summary!C10:C77)),,))),(Summary!E10:E77)) and it showed all of my Summary Sheet's contents. I'm already using a basic subtotal to obtain my filtered values on WkShtA. Thus, I need to show a "linked" subtotal, without filtering the Summary sheet. I hope this makes sense, if not, please ask away, as I'm not real clear on how to better explain it. Again, thank you for your helps. Best. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct and subtotal | Excel Worksheet Functions | |||
Nested subtotal display error | Excel Discussion (Misc queries) | |||
Nested subtotal figures in wrong order | Excel Discussion (Misc queries) | |||
Nested --- Subtotal IF function | Excel Worksheet Functions | |||
Nested Subtotal Formula | Excel Worksheet Functions |