Count Formula

[Katich]

Hello,

I need a formula to give me a count of all items in a column, ignoring hidden rows and duplicate values. Thank you in Advance!

A	B	C	D	E	F	G	H	I	J
1				Total PO's	4
2	5520
3	4580
4	5520
5	1825
6	4890
7	4580

<tbody>
</tbody>

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A7)-ROW(A2),0,1))>0,A2:A7),A2:A7)))

with Control+Shift+Enter.

This assumes they are actual numeric values. If you have text (PO1234), we'll need to tweak that a bit:

=SUM(SIGN(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A7)-ROW(A2),0,1))>0,MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)))

 

[Katich]

Yeah, i may have numbers like the following

PO1234
7234
5678-T

 

[Eric W]

Check my post again. I edited it to include the text version.

 

[Katich]

Awesome! That worked great! Thank you!

 

[Eric W]

Glad to help!