+ Reply to Thread
Results 1 to 2 of 2

Formula for counting ranges

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Mobile, AL
    MS-Off Ver
    Excel 2010
    Posts
    1

    Formula for counting ranges

    Please help!

    I need a formula to count the number of occurances of a range within a column. I have 9,000 rows with number that range from 1-1,000,000. When the number gets above 500,000, I need to count that range as 1. I don't need to count the total number of times a number =>500,000 occurs. Just need the number of ranges from 500,000 to 1,000,000 occurs.

    Does this make sense? For instance, the answer below would be 3.

    458,852
    456,381
    455,653
    532,919
    651,894
    458,852
    456,381
    455,653
    902,403
    901,299
    456,381
    455,653
    899,106
    901,783

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Formula for counting ranges

    Without using any additional columns, you can use an arrayed formula (enter with CONTRL SHFT ENTER instead of ENTER. If done properly, you'll see {} around the formula)

    =SUM(IF(A1:A13<500000, IF(A2:A14>500000,1,0),0))
    Modify the ranges, first range begins at beginning of data, ends 1 short of end of data. 2nd range begins 1 into the data and ends at end of data.
    Does that work for you?
    Last edited by ChemistB; 03-18-2013 at 12:47 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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