+ Reply to Thread
Results 1 to 6 of 6

Sum unique (filtered) values, without a helper column

  1. 02-28-2022, 06:55 PM #1
    arcarc1309
    arcarc1309 is offline
    Registered User
    Join Date
    05-10-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    41

    Sum unique (filtered) values, without a helper column

    I've been chasing my tail all day. I would like a sum of the unique values in column H.

    I'm attempting to use a SUM, FREQUENCY, IF, and SUBTOTAL combination:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This counts the number of unique values, despite using the SUBTOTAL function_num 9 (sum) instead of 3 (count).

    If Col H has the figures below, the formula should add like this 4320+96021+537131+640000, instead of telling me there's 4 unique values.
    4320
    4320
    4320
    96021
    96021
    96021
    537131
    537131
    537131
    640000


    Could someone help? Thanks!!
    Reply With QuoteRegister To Reply
  2. 02-28-2022, 07:01 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,355

    Re: Sum unique (filtered) values, without a helper column

    Hi arcarc1309

    In 365 is a newer function called Unique(). I put your data in H1:H10 and used =SUM(UNIQUE(H1:H10)). Seemed to work. Try out the newer Unique() Function.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 02-28-2022, 07:06 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,454

    Re: Sum unique (filtered) values, without a helper column

    Maybe this:

    =COUNT(UNIQUE(H1:H10))
    =SUM(UNIQUE(H1:H10))

    assuming formula in cell J1
    =COUNT(J1#)
    =SUM(J1#)
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 02-28-2022, 07:26 PM #4
    arcarc1309
    arcarc1309 is offline
    Registered User
    Join Date
    05-10-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Sum unique (filtered) values, without a helper column

    I thought of using UNIQUE too, but it's the filter that's killing me. I need that to change based on what's filtered in the other existing columns and it's not a quantifiable list of things in those other columns.

    That would be wayyyy to easy. Haha.
    Last edited by arcarc1309; 02-28-2022 at 07:29 PM.
    Reply With QuoteRegister To Reply
  5. 02-28-2022, 07:36 PM #5
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,998

    Re: Sum unique (filtered) values, without a helper column

    =sum(if(frequency(if(subtotal(9,offset(h3:h110,row(h3:h110)-row(h3),0,1)),match("~"&h3:h110,h3:h110&"",0)),row(h3:h110)-row(h3)+1),h3:h110))
    Reply With QuoteRegister To Reply
  6. 02-28-2022, 07:41 PM #6
    arcarc1309
    arcarc1309 is offline
    Registered User
    Join Date
    05-10-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Sum unique (filtered) values, without a helper column

    I was sooo close!

    Thank you for your expertise!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Highlight alternate unique values in a filtered column
    By elbarauljr in forum Excel General
    Replies: 4
    Last Post: 09-15-2020, 07:25 PM
  2. Extract unique values from filtered column
    By Journeyman3000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2020, 09:38 PM
  3. Extracting unique values from filtered column to array
    By Journeyman3000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2020, 03:22 AM
  4. Sort by helper column even if values in helper are alike
    By NoSleepForMe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2016, 04:14 AM
  5. Count unique, visible values in a filtered column
    By kajakk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-07-2013, 08:42 AM
  6. count unique values in filtered column
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-09-2013, 10:40 AM
  7. Count Unique Values in a Filtered Column
    By mashley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2008, 10:56 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1