S
SteveDB1
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:C77)-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.
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:C77)-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.