Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct and subtotal Marcelo Excel Worksheet Functions 1 March 21st 07 04:26 PM
Nested subtotal display error phil Excel Discussion (Misc queries) 3 October 24th 06 09:16 AM
Nested subtotal figures in wrong order Nested subtotal figures in wrong order Excel Discussion (Misc queries) 7 May 19th 06 03:56 AM
Nested --- Subtotal IF function Lisa Beach Excel Worksheet Functions 3 November 23rd 05 08:58 PM
Nested Subtotal Formula nickimu Excel Worksheet Functions 1 August 30th 05 04:21 PM


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"