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?

 

[greenman]

why don't you use pivot?

 

[T. Valko]

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

Book1
	A	B	C	D	E
1	Week Num	Order Num	_	Week Num	Uniques
2	1	103	_	1	2
3	1	105	_	2	2
4	2	104	_	3	1
5	2	104	_	4	1
6	2	109	_	5	1
7	2	109	_	6	0
8	3	110	_	7	2
9	3	110	_	8	1
10	4	104	_	9	2
11	5	101	_	10	1
12	7	102	_	_	_
13	7	102	_	_	_
14	7	102	_	_	_
15	7	107	_	_	_
16	8	110	_	_	_
17	9	107	_	_	_
18	9	110	_	_	_
19	10	103	_	_	_
20	10	103	_	_	_
Sheet1

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$20=D2,B$2:B$20),B$2:B$20),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Mudbutt]

Hmm I'm just getting 0's for the uniques. Does it matter that in my week column it's text (i.e. "Week 35") instead of a number? Does that throw it off?

 

[T. Valko]

Hmm I'm just getting 0's for the uniques. Does it matter that in my week column it's text (i.e. "Week 35") instead of a number? Does that throw it off?

It would make a difference if the order nums are text.

Try this...

Book1
	A	B	C	D	E
1	Week Num	Order Num	_	Week Num	Uniques
2	Week 1	103	_	Week 1	2
3	Week 1	105	_	Week 2	2
4	Week 2	104	_	Week 3	1
5	Week 2	104	_	Week 4	1
6	Week 2	109	_	Week 5	1
7	Week 2	109	_	Week 6	0
8	Week 3	110	_	Week 7	2
9	Week 3	110	_	Week 8	1
10	Week 4	104	_	Week 9	2
11	Week 5	101	_	Week 10	1
12	Week 7	102	_	_	_
13	Week 7	102	_	_	_
14	Week 7	102	_	_	_
15	Week 7	107	_	_	_
16	Week 8	110	_	_	_
17	Week 9	107	_	_	_
18	Week 9	110	_	_	_
19	Week 10	103	_	_	_
20	Week 10	103	_	_	_
Sheet1

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$20=D2,MATCH(B$2:B$20,B$2:B$20,0)),ROW(B$2:B$20)-ROW(B$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Mudbutt]

Yeah, my order numbers are being stored as text. I'm still testing to see if your latest formula will work. It's taking forever for excel to process it because there are so many lines. It's been 10 minutes, so I'm not sure if it will work.

 

[T. Valko]

Yeah, my order numbers are being stored as text. I'm still testing to see if your latest formula will work. It's taking forever for excel to process it because there are so many lines. It's been 10 minutes, so I'm not sure if it will work.

You might be better off using a pivot table.

I never use pivot tables so someone else will need to help get it set up. Try starting a new thread and use a subject line something like: Need Pivot Table to count unique items in large dataset. Maybe I'll even learn how to do it!

 

[Mudbutt]

I'll try, but so far i haven't been able to figure out how to count unique values in a pivot. This is a tough one.

 

[greenman]

Do pivot on Column A only and count on the same one.

 

[circledchicken]

I'll try, but so far i haven't been able to figure out how to count unique values in a pivot. This is a tough one.

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"]0[/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"]0[/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"]0[/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"]0[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]Week 7[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]0[/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"]0[/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