+ Reply to Thread
Results 1 to 7 of 7

sumproduct using a filter

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    sumproduct using a filter

    I tried to utilize the solutions shown here but to no avail.
    http://excelforum.com/showthread.php?t=593049

    my current formula is =SUMPRODUCT($W2:$W138,H2:H138)

    I have 9 buttons assigned to macros to filter in various ways.
    They may filter based on Column E or Column F
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    I would like my SUMPRODUCT to calulate only the filtered data.
    is it possible?

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    This link should help

    http://www.contextures.com/xlFunctions04.html

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    That's another one I tried to apply, but
    I don't see how it can work, because I'm not Subtotalling Column H, I'm subtotalling the SUMPRODUCT of H * W .
    but then only on the visible cells

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    How about posting an example

    VBA Noob

  5. #5
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    I tried to reduce the file as small as I could to still give you the general idea.
    Click any of the buttons at the top to filter.
    Thanks
    Attached Files Attached Files

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try this array (Ctrl + shift + enter)

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(H2:H13,ROW(H2:H13)-MIN(ROW(H2:H13)),,1)),--(H2:H13)*(W2:W13))

    Swap the words None to 0

    VBA Noob
    Last edited by VBA Noob; 04-04-2007 at 05:34 PM.

  7. #7
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    { =SUMPRODUCT(SUBTOTAL(3,OFFSET(H2:H13,ROW(H2:H13)-MIN(ROW(H2:H13)),,1)),--(H2:H13)*(W2:W13)) }

    Works great, can you explain it to me?
    I’ve dissected it and am more befuddled than when I began!
    Here's my thought process:


    OFFSET(H2:H13,ROW(H2:H13)-MIN(ROW(H2:H13)),,1)
    Returns a refernce to Cell H2, regardless if H2 is visible or hidden.
    But within the Subtotal function it must really be returning the first cell in the visible data?

    as an aside...at the end of the formula, why is the Column Qty missing? )), ? ,1)
    I thought it was required.
    I put a 1 in to clarify things for myself, still seems to calculate properly. )),1,1)


    (SUBTOTAL(3,OFFSET(H2:H13,ROW(H2:H13)-MIN(ROW(H2:H13)),,1))
    If H2 is visible, this returns 1 – Why, because there’s only one cell in the Offset formula?
    If H2 is NOT visible, this returns 0
    However, if my assumption above is that the OFFSET returns the first cell in the visible data, then this must always return 1.

    Lastly, the double neg at --(H2:H13)*(W2:W13) confuses me.
    (BTW, the formula returns the same correct total WITH or WITHOUT the dbl neg.)

    With the double neg seems like the formula says:
    SUMPRODUCT ( 1, or maybe 1 for each visible cell? * 1 * value in W )
    but if the 1 means "true" then SUMPRODUCT (H * 1 * W)

    Without the double neg seems like the formula says:
    SUMPRODUCT ( 1, or maybe 1 for each visible cell? * value in H * value in W )
    but if the 1 means "true" then SUMPRODUCT (H * H * W)

    Thanks for your time.



    PS. does “Credo Elvem ipsum etian vivere” mean what I think it does and are you truly a believer!?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1