+ Reply to Thread
Results 1 to 11 of 11

Sum Unique Values with Conditions

  1. #1
    Registered User
    Join Date
    04-12-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Sum Unique Values with Conditions

    Excel 2010, I have a sheet that I'm using as a base for a sheet of metrics.

    Sheet1 has my data, with named ranges, and Sheet2 has my metrics on it.

    I have multiple client names (Named range = Client_Name)that could be associated with a row of data, and I need to know how many unique entries occur here after filtering for another criteria (where named range Client_ClientNonClientRelated = On Site Client Related Time).

    I'm not sure how to go about this. This is what I've tried, but I get a 0 as a result.

    =SUM(IFS(Client_ClientNonClientRelated,"=On Site Client Related Time",FREQUENCY(MATCH(ClientName,ClientName,0),MATCH(ClientName,ClientName,0))>0,1))

    Any suggestions?
    Last edited by tdemana; 04-13-2011 at 11:04 AM. Reason: Problem Solved

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum Unique Values with Conditions

    Try:

    Please Login or Register  to view this content.
    confirm with CTRL+SHIFT+ENTER not just ENTER.

    Note: If On Site Client Related Time is a text string to search for, then you need to wrap quotes around it:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-12-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sum Unique Values with Conditions

    That worked, and gave me the correct information, however, it's making the whole system slow, and giving me the message Calculating: (4 Processor(s)) 0%.

    Any reason for, or way around this?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum Unique Values with Conditions

    How large are the ranges?

  5. #5
    Registered User
    Join Date
    04-12-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sum Unique Values with Conditions

    About 50,000 rows.

    I'm assuming that if this were cut down, it would work quicker?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum Unique Values with Conditions

    Yes, array formulas are not very efficient and should be used with minimal amount of data.

    You can possibly use a helper column to identify the unique information and then count the marks in that column....

    eg.

    If your ClientName is in A2:A50000 and your Client_ClientNonClientRelated is in B2:B50000
    then in C2 you could enter a formula like:

    =IF(AND(B2="On Site Client Related Time",COUNTIFS(B$2:B2,"On Site Client Related Time",A$2:A2,A2)=1),COUNT(C$1:C1)+1,"")

    copied down

    Then =MAX(C2:C50000) should give you unique count.

    adjust ranges to suit.

  7. #7
    Registered User
    Join Date
    04-12-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sum Unique Values with Conditions

    So, I have it working like a charm now, but I have one question left. Which parts of the formula do I modify to add one, or two more criteria (in addition to the one that's listed there now?

    Thanks for the assistance, you've been fantastic so far!

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum Unique Values with Conditions

    It depends on the criteria (I mean is it an AND or an OR type of criteria). Can you give example of what else to check? Also, which formula did you use?

  9. #9
    Registered User
    Join Date
    04-12-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sum Unique Values with Conditions

    I ended up using this one:

    =COUNT(1/FREQUENCY(IF(Client_ClientNonClientRelated="On Site Client Related Time",IF(Client_Name<>"",MATCH(Client_Name,Client_Name,0))),ROW(Client_Name)-ROW(OFFSET(Client_Name,,,1,1))+1))

    I removed a bunch of old archived data, so I was only looking at current stuff, and shortened my ranges to 5000 rows, which saved and processed almost instantaneously.

    I'm looking to add more AND criteria. I'm going to use one version of the formula with 1 criteria, another with 2 criteria, and another with 3 criteria. All AND functions.

    So, it would almost be the same as my Client_ClientNonClientRelated=" ********** " just with additional ranges, and additional specific text.


    I'm trying to follow the breakdown of how all of this formula is working, so I can learn for the future.

    Thanks again.

  10. #10
    Registered User
    Join Date
    04-12-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Sum Unique Values with Conditions

    As an example, my other two criteria might be Client_ClientNonClientAction="Project Manager" with Client_ClientNonClientSubAction="Plan and Execute"

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum Unique Values with Conditions

    The easiest way is to enclose each condition within parentheses and then join the conditions with * which works as AND in array formulas.

    E.g

    Please Login or Register  to view this content.

+ 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