Countif Unique values only?

[Mudbutt]

I'm using Excel 2008. I have a production order # column that has duplicates in it. I also have a week num column with multiple weeks. I need to do a count on unique production order #'s by Week. I'm not sure how to do that.

I found this formula =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) which would help me get the count for all the orders, but it doesn't get it to me by week. Can anyone help me add that criteria?

 

[T. Valko]

Hi,

Try adding an extra column to your dataset like this:


Sheet1[TABLE="class: html-maker-worksheet"]
<TBODY>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[/TR]
[TR]
[TH]1
[/TH]
[TD]Week Num
[/TD]
[TD]Order Num
[/TD]
[TD]WeekOrder
[/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD]Week 1
[/TD]
[TD="align: right"]103
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD]Week 1
[/TD]
[TD="align: right"]105
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]4
[/TH]
[TD]Week 2
[/TD]
[TD="align: right"]104
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]5
[/TH]
[TD]Week 2
[/TD]
[TD="align: right"]104
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]6
[/TH]
[TD]Week 2
[/TD]
[TD="align: right"]109
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]7
[/TH]
[TD]Week 2
[/TD]
[TD="align: right"]109
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]8
[/TH]
[TD]Week 3
[/TD]
[TD="align: right"]110
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]9
[/TH]
[TD]Week 3
[/TD]
[TD="align: right"]110
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]10
[/TH]
[TD]Week 4
[/TD]
[TD="align: right"]104
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]11
[/TH]
[TD]Week 5
[/TD]
[TD="align: right"]101
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]12
[/TH]
[TD]Week 7
[/TD]
[TD="align: right"]102
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]13
[/TH]
[TD]Week 7
[/TD]
[TD="align: right"]102
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]14
[/TH]
[TD]Week 7
[/TD]
[TD="align: right"]102
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]15
[/TH]
[TD]Week 7
[/TD]
[TD="align: right"]107
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]16
[/TH]
[TD]Week 8
[/TD]
[TD="align: right"]110
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]17
[/TH]
[TD]Week 9
[/TD]
[TD="align: right"]107
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]18
[/TH]
[TD]Week 9
[/TD]
[TD="align: right"]110
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]19
[/TH]
[TD]Week 10
[/TD]
[TD="align: right"]103
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]20
[/TH]
[TD]Week 10
[/TD]
[TD="align: right"]103
[/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]
Excel 2010

Cell	Formula
C2	=N(NOT(SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2=B2))>1))

<TBODY>
[TD="bgcolor: #ffffff"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<TBODY>[TR]

</TBODY>

[/TD]
[/TR]
</TBODY>[/TABLE]

• Now create a PivotTable and the new helper column can be used for the unique count. There is a nice article that describes the technique here:
  • Excel Pivot Table Tutorial -- Grouping Data
• SUMIF/SUMIFS combinations may be faster than SUMPRODUCT.
• As an alternatively to the PivotTable you can ofcourse also use a formula solution with the helper column using SUMIF/SUMIFS as appropriate.
• I'm not sure if your version of Excel supports VBA, but if it does then an efficient unique count UDF solution is presented in this very useful whitepaper by Charles Williams (you would need to tweak it a little bit to account for your extra column):

• Improving Performance in Excel 2007

If you have 200,000 rows of data that helper formula alone will bring things to a crawl!

 

[circledchicken]

If you have 200,000 rows of data that helper formula alone will bring things to a crawl!

Ok, some better alternatives might be (although again not sure if Excel 2008 has support for these):

• Use SQL within Excel - this should be a simple and fast solution. The syntax would be something like this.
  

  SELECT `Week Num`, COUNT(*) AS [Uniques]
       FROM (SELECT DISTINCT `Week Num`, `Order Num`
             FROM [B]DefinedName[/B])
       GROUP BY `Week Num`

  There are many examples of how to implement this in a worksheet e.g. here is a good one from Fazza: http://www.mrexcel.com/forum/excel-questions/579045-count-unique-list-criteria.html#post2864652

• Use PowerPivot, creating a new measure of the form:
  

  =COUNTROWS(            
     DISTINCT( Table[Order Num] ) )

 

[Teethless mama]

I'm using Excel 2008. I have a production order # column that has duplicates in it. I also have a week num column with multiple weeks. I need to do a count on unique production order #'s by Week. I'm not sure how to do that.

I found this formula =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) which would help me get the count for all the orders, but it doesn't get it to me by week. Can anyone help me add that criteria?

Try this:

Excel Workbook
	A	B	C	D	E	F
1	Week Num	Order Num	Unique
2	Week 1	103	1
3	Week 1	105	1		Pivot table
4	Week 2	104	1		Row Labels	Sum of Unique
5	Week 2	104	0		Week 1	2
6	Week 2	109	1		Week 10	1
7	Week 2	109	0		Week 2	2
8	Week 3	110	1		Week 3	1
9	Week 3	110	0		Week 4	1
10	Week 4	104	1		Week 5	1
11	Week 5	101	1		Week 7	2
12	Week 7	102	1		Week 8	1
13	Week 7	102	0		Week 9	2
14	Week 7	102	0		Grand Total	13
15	Week 7	107	1
16	Week 8	110	1
17	Week 9	107	1
18	Week 9	110	1
19	Week 10	103	1
20	Week 10	103	0
Sheet1

 

[T. Valko]

Try this:

Spreadsheet Formulas

Cell Formula C2 =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,0) <TBODY> </TBODY>

<TBODY>
</TBODY>

That's only marginally better than the SUMPRODUCT formula.

We don't know how much data the OP has but they did say after 10 minutes it was still calculationg so there must be huge amounts of data.

If there are 200,000 rows of data that means you have to enter 200,000 of those formulas.

With 200,000 rows of data this is how many cells those formulas are evaluating by the time you get down to row 200,000...

=SUMPRODUCT(ROW(A1:A200000))

Can this NOT be done in a PT without using a helper column? I never use PTs so I don't know.

 

[circledchicken]

Can this NOT be done in a PT without using a helper column? I never use PTs so I don't know.

- you really should at least try using them. They make life so much easier once you get used to them and really only takes a very small amount of time to get used to. But you are a formula super-genius, it might be that they are just too easy for you!

In answer to the question:

• No, at least not directly with a standard data source as far as I am aware - confirmed here: Excel Pivot Table Tutorial -- Grouping Data
• However, it is easy to do it with a PivotTable based on an external data source (which doesn't really have to be 'external' - in this case will just be a defined name assigned to the table in the worksheet). As per post #12, the SQL required is very simple and easy to implement.
• Another alternative is just use a query directly - no need to embed it in a PivotTable - it also allows you to refresh it just like a PivotTable and is also very easy to set up.
• PowerPivot Measures also allow you to do this easily and quickly as per post #12
• As mentioned though, not sure how many of these options are actually available in Excel 2008.

 

[T. Valko]

- you really should at least try using them. They make life so much easier once you get used to them and really only takes a very small amount of time to get used to. But you are a formula super-genius, it might be that they are just too easy for you!

Sure, I've tried them but I find I can pretty much do what a PT does and it takes less time for me to do it with formulas.

However, this thread is a good example of the drawback of using certain types of formulas on huge datasets. OTOH, it looks like a PT is not so easy to setup for this type of analysis. It seems that PTs are more suitable for "basic" calculations.

The bottom line: Excel sorely needs an EFFICIENT array of functions to deal with uniques. These types of questions are asked almost daily so there is a definite need for these types of functions. This should also be a basic function of a PT.

 

[circledchicken]

Sure, I've tried them but I find I can pretty much do what a PT does and it takes less time for me to do it with formulas.

Maybe for you haha ... but I think I have to respectfully disagree here. With a bit of experience they become intuitive and extremely easy to set up and use. Even with your formula expertise, I find it hard to imagine that where used appropriately an equivalent formula setup would be easier than the corresponding PivotTable solution. Other major advantages are they are flexible so you can slice your data and many ways very quickly, and can't really 'mess up' and they are a 'trial and error' feature by nature.

However, this thread is a good example of the drawback of using certain types of formulas on huge datasets. OTOH, it looks like a PT is not so easy to setup for this type of analysis. It seems that PTs are more suitable for "basic" calculations.

Also respectfully disagree. This unique count solution is very easy to set up - Fazza has a lot of posts on SQL/PivotTable solutions and they really are very easy to follow (similar to the instructions you see on something like 'How to install a UDF'). Even if you are new to SQL (like me) the syntax is probably still easier to follow than breaking down the many alternative Excel unique count formulae options.

The bottom line: Excel sorely needs an EFFICIENT array of functions to deal with uniques. These types of questions are asked almost daily so there is a definite need for these types of functions. This should also be a basic function of a PT.

Yeah, some built in functions would be good! The COUNTU UDF from Charles Williams is very fast with large datasets too. PowerPivot measures are also a good, easy solution I think - and it looks likely that it will be included as standard with future versions of Excel.

 

[Mudbutt]

Thank you so much guys! Your solutions with the unique column + PT worked out. I have 55,000 rows of data so it did take some time to process, but it is manageable. I am only temporarily doing this report in Excel, as I'm trying to get the raw data pushed to a SQL server and pull it into Business Objects to do the calculations and dirty work for my inevitable dashboard.

 

[circledchicken]

Thank you so much guys! Your solutions with the unique column + PT worked out. I have 55,000 rows of data so it did take some time to process, but it is manageable. I am only temporarily doing this report in Excel, as I'm trying to get the raw data pushed to a SQL server and pull it into Business Objects to do the calculations and dirty work for my inevitable dashboard.

You're welcome!

 

[T. Valko]

Thank you so much guys! Your solutions with the unique column + PT worked out. I have 55,000 rows of data so it did take some time to process, but it is manageable. I am only temporarily doing this report in Excel, as I'm trying to get the raw data pushed to a SQL server and pull it into Business Objects to do the calculations and dirty work for my inevitable dashboard.

Good deal. Thanks for the feedback!