If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|