Count if without duplicates and other values

[CarlCarlos]

Hi all,

I am looking to pull together a spreadsheet that counts up the number of products that I have if they have sales e.g.


PRODUCT Year 1 Sales Year 2 Sales
A 50
A 0
B 20
C 12
D 0
D 16
E 0

As you can see I have the same product duplicated which may or may not have sales. What I need is a count if that counts the number of products that had sales in the year. in this example it would be 4 products.

While I have managed to make a count if that removes duplicates, I am unable to make this exclude duplicates AND no sales values.

Can you please help?

Kindest Regards
Carl Carlos

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C
1	PRODUCT	SALES	4
2	A	50
3	A	0
4	B	20
5	C	12
6	D	0
7	D	16
8	E	0
Sheet1

Cell Formulas
Range	Formula
C1	{=SUM(IF(FREQUENCY(IF(B2:B8<>0,MATCH(A2:A8,A2:A8,0)),ROW(A2:A8)-ROW(A2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CarlCarlos]

Wow a very impressive formula. It works spot on so thank you, but could you please explain how this does what it does?

 

[Andrew Poulsom]

Use the Evaluate Formula tool to see how it works.