Formula to count occurances of decimal value (Easy one)

I need a formula that can simply count the occurances of the decimal value in range (O2-O20)

and write the counts in column Q2 ....see sample

Rep given for a formula that can give those results

Thank you

Can this be done with a formula or do I need a macro ?

Thank you for your effort -It work but not consistently if updated ....added another 1.7 in range O2-O20 so theres 3 and the formula dont update showing 1.7(3) 3 counts.Any idea ?

Still looking for a solution that will update as the data changes

You could do it a lot easier with a helper column.

In P2 and fill down, to give a count of each value in column O

=COUNTIF(O:O,O2)

Then create a pivot table from those 2 columns, with column P as a filter to show only counts greater than 1. Column O as pivottable rows and as a count in the values box, sorted descending.

It's not quite the same visually, but is a lot messy.

See my late reply to your previous question.
You may have entered 1.7 but the other values are 1.66666666666667
Excel can be rather particular at times of comparison.
torachan.

Originally Posted by Bo_Ry

Please try

Formula:

Please Login or Register  to view this content.

I dont get this to work -not working

Originally Posted by torachan

See my late reply to your previous question.
You may have entered 1.7 but the other values are 1.66666666666667
Excel can be rather particular at times of comparison.
torachan.

No its not that its not updating as data changes sadly.If a number changes in O2-O20 then the formula must update automatically in Q2-Q20 thus its not counting correctly

Originally Posted by jason.b75

You could do it a lot easier with a helper column.

In P2 and fill down, to give a count of each value in column O

=COUNTIF(O:O,O2)

Then create a pivot table from those 2 columns, with column P as a filter to show only counts greater than 1. Column O as pivottable rows and as a count in the values box, sorted descending.

It's not quite the same visually, but is a lot messy.

Out of my scope sadly need a formula or macro even -Im not good with pivot tables -but thanks for suggestion

Trying to put the extracted value and the count in the same cell is a bad habit to get into, and like merged cells, totally unnecessary. There are plenty of ways to make it look the same of that is what you want.

In Q2, array confirmed with Shift Crl Enter. Drag down as needed.

=IFERROR(MODE.SNGL(IF(ISNUMBER(MATCH(ROUND($O$2:$O$18,1),Q$1:Q1,0)),"",ROUND($O$2:$O$18,1))),"")

In R2, fill down parallel to the previous formula.

=IF(Q2="","",COUNTIFS(O:O,">="&(Q2-0.05),O:O,"<"&(Q2+0.05)))

If desired, use a custom format of (0) on column R with left alignment or reduced column widths to make it look the same as your example.

And if you do not want your sheet littered with formula try a macro.
The attached works with the range A1:A140 using the Worksheet_Change event.
Enter numbers in this range as you move from the 'entry cell' the 'uniques' alter in column 'B' and their quantity count updates in 'C'
torachan.