# How does this formula work? {=SUM(1/COUNTIF(B2:B400,B2:B400))}
Why can it fail to return the correct "countdistinct" result?

Hi Hivemind,

I have been using this array formula before to determine the number of items in a list: {=SUM(1/COUNTIF(B2:B400,B2:B400))}

However, I don't actually understand really what it does, could someone explain how it works, please?

The reason this has become important to me (apart from simple curiosity) is that I have now long item lists of several tens of thousands of lines and the result I get is not an integer but a decimal (which for counting distinct items obviously doesn't make sense). So, I assume this formula is a workaround and an approximation to counting items and I would like to know how accurate it is.

Thanks a lot.

PS: Here is a test file. It is an extract of the original data I am working with. I check for empty cells first then do the counting of items, which are project titles in this case. Sheet 1 shows a small deviation, Sheet 2 a significant one, and sheet 3 shows a div/0 error even though there are no empty cells in that column.

So, I don't know if I do something fundamentally wrong or if there is an issue with the data or...

Thanks a lot for looking into this. Best regards. C:\fakepath\Test sheet.ods

You have blank cells and also 0 (zero) values in your range B2:B400. The trick you are using can't work in this case. It's surely pure fun to find out why.

The mentioned trick is rather old. The dangerous automatisms causing the isssue also are.

How many users will have trusted in wrong results having rounded the decimals away (probably suppressing the display by formatting) and the propagation by

`Precision as shown`

e.g.?Spredsheets are

verydangerous. They once were usful. Till Excel came?Please also consider what I wrote in

https://ask.libreoffice.org/en/questi...

No, Lupp, I actually don't, The cells are not integers but text. They are project titles so never zeros. And I filled all empty cells, at least that is what it tells me when I use =COUNTIF(B2:B7451,""). The reason must be a different one. Maybe the titles are too long or something like that?

I updated my question with a file to clarify the issue. It sometimes seems to load quite slowly (i.e. several minutes), sorry for that. It seems to recalculate all formulas and that sucks.

Well, spreadsheets know many tricks to baffle users.

Hope I'll find out.