+ Reply to Thread
Results 1 to 6 of 6

Sum with 5 Conditions in Visible Rows of a Table

  1. #1
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Sum with 5 Conditions in Visible Rows of a Table

    Hello,

    I'm having trouble making a correct formula to sum up some values in Table1 based on multiple criteria and only in visible rows.
    The formula serves as a verification for some data I am reporting and, of course, I need to be sure it is correct.

    I tried various formulas by using SUMPRODUCT, SUBTOTAL, AGGREGATE, but nothing worked.

    See the attached file with my table of data (Table1), Nm that must be excluded from the sum (H2:H6), desired results obtained with SUMIFS (G2:G3), main slicer (Mt), helper slicer (Nm) and not working formula (D1).

    Hopefully someone can help me find a formula that would work.
    To show your appreciation
    Click ★ Add reputation!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum with 5 Conditions in Visible Rows of a Table

    Hi,

    It's generally preferable to avoid a mixture of structured table and standard referencing:

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(INDEX(Table1[Qty],1),ROW(Table1[Qty])-MIN(ROW(Table1[Qty])),)),1-ISNUMBER(MATCH(Table1[Nm],H2:H6,0)))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Sum with 5 Conditions in Visible Rows of a Table

    Thanks, that works great for my sample file and also works if the values in Nm match the values in H2:H6 exactly.
    In my real data however there are partial matches - the AB, CD, EF etc. are just 2 characters of longer strings, eg. XXXXABXXXX or CDZZZZZZ.

    Is it possible to make this to exclude partial matches like in my original-non-working formula?
    Something like "*" & $H$3 & "*" but I can work without ranges, like for example "*AB*", "*CD*"

    Because the values I have in column Nm in the real data are long and sometimes letters are missing from the entire name, but they contain certain strings for sure - eg. the string RNG or TXT somewhere in the entire name in column Nm.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum with 5 Conditions in Visible Rows of a Table

    Then I believe you'll need an array formula**:

    =SUM(IF(SUBTOTAL(102,OFFSET(INDEX(Table1[Qty],1),ROW(Table1[Qty])-MIN(ROW(Table1[Qty])),)),IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(H2:H6),(Table1[Nm]))),ROW(H2:H6)^0)=0,Table1[Qty])))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  5. #5
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Sum with 5 Conditions in Visible Rows of a Table

    This works great, thank you very much.

    I still have soooo so much to learn.

    Best regards.

    Edit:
    Wanted to give rep for both posts because both were great solutions.
    Didn't know I had to "spread some more" before being able to give more to you, so I wasn't able to do it yet.
    Will do in the future, hopefully I remember.
    Last edited by Mrrrr; 03-12-2020 at 10:34 AM.

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum with 5 Conditions in Visible Rows of a Table

    You're welcome!

    (And don't worry about the rep points. I'm more than happy with what you've given! Cheers!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Remove Visible Rows from Filtered Table
    By dflak in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 01-29-2018, 12:28 PM
  2. Table Object after filtering - Count visible rows & First visible row
    By limalf in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-13-2013, 07:29 PM
  3. How to only count the number of visible rows that meet 2 conditions
    By chaz123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2013, 10:15 AM
  4. Use visible rows from autofilter to build Pivot table. Or use visible rows to Copy/Paste
    By mwhitedesigns in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 10:34 AM
  5. Delete Only Visible Rows in Table
    By djblois1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-12-2011, 03:57 PM
  6. Pivot Table for Visible rows only
    By deepcdiver in forum Excel General
    Replies: 3
    Last Post: 02-18-2009, 02:03 PM
  7. [SOLVED] count only the visible rows in a data table
    By Eric in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-21-2005, 09:05 PM

Tags for this Thread

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