# Count Unique Values based on different Criterias

1. ## Count Unique Values based on different Criterias

Hello all, so i have read the different posts on the forum about this same topic but i found the example given to simple to apply to more complex data sets.
For example take a look at this data set.

Account_Number Tran_Amount SIC Year Month
1234567890 \$98 5413 2009 1
1234567891 \$33 5812 2006 2
1234567892 \$37 5812 2009 1
1234567890 \$41 5411 2008 3
1234567894 \$46 5812 2009 1
1234567892 \$51 5413 2007 8
1234567896 \$57 5411 2009 1
1234567897 \$63 5413 2006 5
1234567890 \$70 5411 2009 1
1234567899 \$78 5812 2009 1

I would like to know how many Account numbers are there that have a SIC of 5812 in year 2009 and month 1. The answer should be 3.
Also, i wood like to know how many Account numbers had transactions in 2009 month 1 for all SIC but 5411. The answer should be 4.

I believe the formula that should be used here is =SUM(IF(FREQUENCY(
but i cannot figure out of to do it properly.

Your help is greatly appreciated.

Thank you,

Morwick

ps: ( i tried to create a table but i couldn't, 5 columns:Acc #, Tran-Amt, SIC, Year, Month )  Register To Reply

2. ## Re: Count Unique Values based on different Criterias

A simple COUNTIFS() should suffice instead of SUM(IF(FREQUENCY(.

See the attached workbook for a solution.

For your second inquiry, your input into G2 should be <>5411.

Hope this helps!  Register To Reply

3. ## Re: Count Unique Values based on different Criterias

If your data is in columns A to E then, if account numbers are actual numbers you could use this formula

=SUM(IF(FREQUENCY(IF(C2:C10=5812,If(D2:D10=2009,IF(E2:E10=1,A2:A10))),A2:A10),1))

For any type of data in column A this longer version should work

=SUM(IF(FREQUENCY(IF(C2:C10=5812,If(D2:D10=2009,IF(E2:E10=1,IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))))),ROW(A2:A10)-ROW(A2)+1),1))

Both need to be confirmed with CTRL+SHIFT+ENTER

Note: I'm assuming that all data is numeric in columns C, D and E - if that isn't the case then you might need quotes around the criteria values, i.e. "5812", "2009" and "1"  Register To Reply

4. ## Re: Count Unique Values based on different Criterias

Thx a lot guys, all the formulas you provided have been helpful. i have much to learn   Register To Reply

5. ## Re: Count Unique Values based on different Criterias Originally Posted by daddylonglegs For any type of data in column A this longer version should work

=SUM(IF(FREQUENCY(IF(C2:C10=5812,If(D2:D10=2009,IF(E2:E10=1,IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))))),ROW(A2:A10)-ROW(A2)+1),1))

Coming back to this for reference, I was wondering if you could provide some clarification. Do you actually need the -ROW(A2)+1 portion of your formula? Or will that negate itself? I like your application of this combination of sum,if,frequency vs. using a helper column, and am trying to wrap my head around it.  Register To Reply

6. ## Re: Count Unique Values based on different Criterias Originally Posted by mcmahobt Do you actually need the -ROW(A2)+1 portion of your formula?
Hello mcmahobt,

Yes, I think you do

The idea is that the "bins" in FREQUENCY function are the same values that the MATCH function can generate, so MATCH(A2:A10,A2:A10,0) generates an array of values which are integers 1 to 9....and ROW(A2:A10)-ROW(A1)+1 generates this array for the bins

{1;2;3;4;5;6;7;8;9}

If those don't match then you may get incorrect results, e.g. assume all 9 rows match the criteria and that A2 and A10 contain "xyz" and A3:A9 all have "abc" - that should be a result of 2 - because there are 2 different values in column A (where criteria match) , but if you remove -ROW(A2)+1 you get an incorrect result - 1

If you use ROW(A2:A10)-1 then that will work, but it makes the formula less robust - if I add rows in the worksheet at the top my range might change to A4:A10 but now ROW(A4:A10)-1 doesn't give me what I need, ROW(A4:A10)-ROW(A4)+1 will still give the correct bins array  Register To Reply

7. ## Re: Count Unique Values based on different Criterias

Makes much more sense. Thanks for taking the time to explain!  Register To Reply

##### Users Browsing this Thread

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

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