 SOLVED

# Excel formula

Hi
I need to count the number of rows in a spreadsheet. But where the number in a row cel is the same I want the formula to ignore that row.

Eg if these were the numbers in the CEO’s in the rows
1
2
3
4
1
4
6
7
8
The formula would return an Answer of 6 rows.

Thank you
8 Replies

# Re: Excel formula

Why 6? I count 7 unique numbers: 1, 2, 3, 4, 6, 7, 8

# Re: Excel formula

I'm not sure how you would get 6 rows, if you're counting distinct cells you would end up with 7 (the formulas assume that the A column is the one you're checking):
=COUNT(UNIQUE(A:A))
1, 2, 3, 4, 6, 7, 8

If you're counting unique cells you end up with 5:
=COUNT(UNIQUE(A:A,,TRUE))
2, 3, 6, 7, 8

# Re: Excel formula

Yes typo should be 7 thanks Hans

# Re: Excel formula

Yes sorry typo should say 7 not 6. Thanks Daigle
best response confirmed by Buzzard2406 (Occasional Contributor)
Solution

# Re: Excel formula

As variant

``=SUM(--(FREQUENCY(A:A,A:A)>0) )``

shall work for any version.

# Re: Excel formula

Perfect thank you Sergei

# Re: Excel formula

@Buzzard2406 , you are welcome, glad it helped

# Re: Excel formula

100% it cut 32,000 rows down to 12830