Unique Values based on Another Column having values >0

[JBurton1986]

Hi,

I need a quick formula which is saved by a Pivot Table but I would like to have Formula (Maybe Sumproduct?) which counts the number of unique entries in column A but only shows the number if there is a value which is greater than 0 in column B.

For instance 700 is repeated several times in column A and has 2 records with values greater than 0 in column B. I would like a formula to say that 700 is counted once rather than twice. Hope that makes sense.

I have attached the file. Basically for Week 1. I have 5 unique Acc_No but only 4 have a value which is greater than 0 in Week 1. I want to return 4.

Can you help?

Thanks

[XOR LX]

Hi,

For Week 1, this array formula**:

=SUM(IF(FREQUENCY(IF(B3:B10>0,MATCH(A3:A10,A3:A10,0)),ROW(A3:A10)-MIN(ROW(A3:A10))+1),1))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[JBurton1986]

Thanks XOR LX. If I had blanks which I wanted to exclude from Column A where would I put the <>""?

Thanks for the quick response.

James

[Tony Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B3:B10>0,A3:A10),A3:A10),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.

[JBurton1986]

Thanks Tony. And If I was to exclude potential blanks would you be able to let me know how I can do that?

Thanks

James

[Tony Valko]

If the Acc_No's really are numbers then empty cells should not have any effect on the formula:

Data Range

	A	B	C	D	E
2	Acc_No	Week 1	Week 2	-----	Count
3	700	0	1		3
4	700	1	1
5	700	1	3
6	701	1	2
7	701	1	2
8	703	0	0
9	7003	1	1
10		2	0
11

This array formula entered in E3:

=SUM(IF(FREQUENCY(IF(B3:B100>0,A3:A100),A3:A100),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.

[XOR LX]

Ignore my solution. It works, but I was on automatic, and of course your values are already numeric, so Tony's solution is far more economical.

Regards

[JBurton1986]

I have it thanks both.

=SUM(IF(FREQUENCY(IF(B3:B11>0,IF(A3:A11="",0,A3:A11)),A3:A11),1))

Cheers