+ Reply to Thread
Results 1 to 16 of 16

Formula to count occurances of decimal value (Easy one)

  1. #1
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Formula to count occurances of decimal value (Easy one)

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

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to count occurances of decimal value (Easy one)

    Please try at Q2 Press Ctrl+Shift+Enter and drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Formula to count occurances of decimal value (Easy one)

    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

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to count occurances of decimal value (Easy one)

    Please try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to count occurances of decimal value (Easy one)

    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.

  7. #7
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,304

    Re: Formula to count occurances of decimal value (Easy one)

    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.

  8. #8
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Formula to count occurances of decimal value (Easy one)

    Quote Originally Posted by Bo_Ry View Post
    Please try

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I dont get this to work -not working

  9. #9
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Formula to count occurances of decimal value (Easy one)

    Quote Originally Posted by torachan View Post
    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

  10. #10
    Forum Contributor
    Join Date
    07-01-2012
    Location
    ZA
    MS-Off Ver
    Excel 2013
    Posts
    300

    Re: Formula to count occurances of decimal value (Easy one)

    Quote Originally Posted by jason.b75 View Post
    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

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to count occurances of decimal value (Easy one)

    How does it not work?


    Untitled.png
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Formula to count occurances of decimal value (Easy one)

    Create a named range "MyList" that refers to Range 02:O18

    Formula for Q2, Entered using Ctrl Shift Enter

    =IF(ROW()>COUNT(INDEX(MyList,N(IF({1},MODE.MULT(IF(MATCH(MyList,MyList,0)=ROW(MyList)-MIN(ROW(MyList))+1,(ROW(MyList)-MIN(ROW(MyList))+1)*{1,1})))),1)),"",SMALL(INDEX(MyList,N(IF({1},MODE.MULT(IF(MATCH(MyList,MyList,0)=ROW(MyList)-MIN(ROW(MyList))+1,(ROW(MyList)-MIN(ROW(MyList))+1)*{1,1})))),1),ROW()-1)& "("&COUNTIF(MyList,SMALL(INDEX(MyList,N(IF({1},MODE.MULT(IF(MATCH(MyList,MyList,0)=ROW(MyList)-MIN(ROW(MyList))+1,(ROW(MyList)-MIN(ROW(MyList))+1)*{1,1})))),1),ROW()-1))&")")
    Attached Files Attached Files
    Last edited by mehmetcik; 02-16-2019 at 12:32 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Formula to count occurances of decimal value (Easy one)

    Try this
    In Q2
    Please Login or Register  to view this content.
    In R2
    Please Login or Register  to view this content.
    In S2
    Please Login or Register  to view this content.
    Drag down all.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Formula to count occurances of decimal value (Easy one)

    If you dont want to use helper columns, In M2 then drag down
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to count occurances of decimal value (Easy one)

    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.
    Attached Files Attached Files

  16. #16
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,304

    Re: Formula to count occurances of decimal value (Easy one)

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. 90 day rolling formula to count occurances.
    By alaz88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-28-2023, 02:35 PM
  2. [SOLVED] Formula to count occurances and put multiple results in same cell
    By Stevejb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2017, 05:22 PM
  3. [SOLVED] Count unique occurances formula
    By lreed in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-21-2017, 07:29 PM
  4. Replies: 5
    Last Post: 06-07-2016, 02:07 PM
  5. Easy count formula?
    By Nutorious in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-26-2013, 07:06 AM
  6. Easy way to do it - Formula to count number of repetitions in a table
    By virtualdark in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-17-2013, 07:54 AM
  7. Replies: 5
    Last Post: 05-11-2012, 03:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1