count if...

[el c]

hi

Do you know how i can count how many times a number is shown in a column "item" but for each "order" counting as once, like the example? :

Book3.xlsx
	A	B	C	D	E
1	Orders	Item
2	1	33
3	1	33		total of 33:	2
4	1	22
5	2	55
6	2	33
7	2	22
Sheet1

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E
1	Orders	Item
2	1	33
3	1	33		total of 33:	2
4	1	22
5	2	55
6	2	33
7	2	22
Sheet1

Cell Formulas
Range	Formula
E3	E3	=COUNT(UNIQUE(FILTER($A$2:$A$7,$B$2:$B$7=33)))

 

[el c]

Try:

Book1
	A	B	C	D	E
1	Orders	Item
2	1	33
3	1	33		total of 33:	2
4	1	22
5	2	55
6	2	33
7	2	22
Sheet1

Cell Formulas
Range	Formula
E3	E3	=COUNT(UNIQUE(FILTER($A$2:$A$7,$B$2:$B$7=33)))

An error message apears and it says : That function isn't valid

 

[AhoyNC]

Try:
This is an array function and must be entered with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D	E
1	Orders	Item
2	1	33
3	1	33		total of 33:	2
4	1	22
5	2	55
6	2	33
7	2	22
Sheet1

Cell Formulas
Range	Formula
E3	E3	{=SUM(IF(FREQUENCY(IF($B$2:$B$7=33,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Another option

+Fluff.xlsm
	A	B	C	D	E
1	Orders	Item
2	1	33
3	1	33		33	3
4	1	22
5	2	55
6	2	33
7	2	22
8	3	22
9	3	55
10	3	33
List

Cell Formulas
Range	Formula
E3	E3	=SUM(--(FREQUENCY(IF(B2:B10=D3,A2:A10),A2:A10)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.