Count unique records in one column based on information in 2nd column

[nairnin]

Good Morning,


I am trying to count unique records in column A based on whether Column H is a positive number.


If column H = $0.00 I do not want to count the corresponding record number in Column A.


I have used the following formula to count the unique records but I cannot figure out how to do it based on the second column criteria.


=SUM(IF(FREQUENCY(A:A,A:A)>0,1))

 

[Montrey]

Use countif or sumif

 

[nairnin]

Thank you for the suggestion. I have tried both of those and they are not working.

 

[Luke M]

As this type of question has been asked many times before, there's several threads with solutions to them. I recommend starting there:

http://chandoo.org/forums/topic/formula-to-count-unique-values-with-criteria


The only difference is that instead of using a =Something condition, you're using a >0 Criteria.

 

[shailyog]

Copy this formula in your result column row 1 and press Ctrl Shift Enter all together. copy down the formula as required and then sum the values in the end.


=IF(COUNTIF($A$1:A1,A1)>1*(h1>0),0,1)

 

[nairnin]

Thank you Shailyog, however this does not count unique records in column A. Luke I have looked at the other posts and when I try to use them, excel is giving me an error.


Here is what my report looks like:


A B C D E F G H

Account number Amount

1 … … … … … … $0.00

2 … … … … … … $3,500.00

3 … … … … … … $4,000.00

4 … … … … … … $4,500.00

5 … … … … … … $5,500.00

5 … … … … … … $5,000.00

6 … … … … … … $6,000.00

7 … … … … … … $7,000.00


The answer I am looking for in this senario is 6. Also the answer is going in column H below the informaton


Thank you so much for the help with this!

 

[Montrey]

What version of Excel are you using?

 

[nairnin]

I am using 2010.

 

[Luke M]

This formula will produce the correct answer to example.

=SUM(IF(FREQUENCY(IF(H1:H10>0,MATCH(A1:A10,A1:A10,0)),ROW(A1:A10)-ROW(H1)+1),1))


Adjust array sizes as needed. Confirm formula using Ctrl+Shift+Enter not just Enter.

 

[shailyog]

@Nairnin


The formula (the one in my previous post) works only on unique Text values not on numbers.