# Sum Unique Values Based on Other Column Criteria

1. ## 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. ## 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.

3. ## 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. ## Re: Sum Unique Values Based on Other Column Criteria

See posted attachment below. Thanks in advance!

5. ## Re: Sum Unique Values Based on Other Column Criteria

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

Control+shift+enter, not just enter:
``Please Login or Register  to view this content.``

6. ## 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.

7. ## Re: Sum Unique Values Based on Other Column Criteria

Sure did! Thanks so much.

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