+ Reply to Thread
Results 1 to 7 of 7

Sum Unique Values Based on Other Column Criteria

  1. #1
    Registered User
    Join Date
    05-06-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Red face Sum Unique Values Based on Other Column Criteria

    Hi!

    I'm currently working with a file that I'm trying to use to sum unique customer numbers based on two separate criteria: month (listed in a column the same tab as the customer id data and month indicated by a number i.e. 4 = April) and product type (A or B which is pulled from a tab in which the data is not typed in text but is a result of a vlookup from a different tab--therefore, the value is a result of a vlookup rather than a hardcoded value).

    I am using the following formula to sum the unique customer ids in April, which is working fine:

    =SUMPRODUCT(1/COUNTIF(Data!Y:Y,Data!Y2:Y2500),--((Data!B2:B2500=4))) --> Customer ID in Data tab column Y and Month number in Column B.

    I am usin gthe same forumla as follows to try to sum the unique customer IDs who purchased product category A:

    =SUMPRODUCT(1/COUNTIF(Data!Y:Y,Data!Y2:Y2500),--((Data!I2:I2500="A"))) --> Customer ID in Data tab column Y and Product category A/B in Column I (but result of Vlookup from separate tab).

    I'm erroring out with a #N/A.

    Is it because I need a different formula or because my product category I'm pulling is based on a vlookup? Anyone have a solution they can help with?



    Thanks!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,375

    Re: Sum Unique Values Based on Other Column Criteria

    It shouldn't matter whether "A" is manually entered or brought there through a VLOOKUP. Something else must be going on. It is a #NA and not a #DIV/0 error? Perhaps there are no exact matches, i.e. one or the other has blank spaces in it? Your formula worked for me but I do not have your data. If you can upload it (Go Advanced> Manage Attachments) that might help troubleshoot.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-06-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum Unique Values Based on Other Column Criteria

    Example File - Formula Error.xlsx

    I have attached the sample file. My apologies for all the appending/workbook protection. Should be enough for you to see what it's doing.

  4. #4
    Registered User
    Join Date
    05-06-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum Unique Values Based on Other Column Criteria

    See posted attachment below. Thanks in advance!

  5. #5
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: Sum Unique Values Based on Other Column Criteria

    Your upload is full of colors thwarting reading...

    If you want to do a conditional unique count...

    Control+shift+enter, not just enter:
    Please Login or Register  to view this content.
    Last edited by Aladin Akyurek; 05-06-2013 at 03:23 PM.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,375

    Re: Sum Unique Values Based on Other Column Criteria

    The problem is all the #N/A's you have on your Sheet "Data" They are in Col H but not Col B. Update your vlookup to
    =IFERROR(VLOOKUP($E2,'Sku Ref'!$A:$J,5,FALSE),"") copied down.
    Did that fix your problem?

  7. #7
    Registered User
    Join Date
    05-06-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum Unique Values Based on Other Column Criteria

    Sure did! Thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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