SOLVED

Excel formula

Copper Contributor
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

@Buzzard2406 

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

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
Yes typo should be 7 thanks Hans
Yes sorry typo should say 7 not 6. Thanks Daigle
best response confirmed by Buzzard2406 (Copper Contributor)
Solution

@Buzzard2406 

As variant

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

shall work for any version.

Perfect thank you Sergei

@Buzzard2406 , you are welcome, glad it helped

100% it cut 32,000 rows down to 12830 :beaming_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by Buzzard2406 (Copper Contributor)
Solution

@Buzzard2406 

As variant

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

shall work for any version.

View solution in original post