+ Reply to Thread
Results 1 to 2 of 2

count the number of times a number appears in a specific range

  1. #1
    Registered User
    Join Date
    10-21-2011
    Location
    baltimore,md
    MS-Off Ver
    Excel 2003
    Posts
    53

    count the number of times a number appears in a specific range

    Hey
    I was having some trouble with this macro hoping I could get some assistance!

    So, starting with cell D254 look for the following values: 110, 130, 150, and 160.
    in the cells D254, F254, H254, J254, and L254. then go to D255 and do the same things for F, H, J, and L255.
    Continue this count untill Line 1454 and put the totals for the four numbers at the end of the document (preferably coluumns a, b, c and d

    One caveat is to not add an instance to the total if the previous checked cell has the same value.
    (so if F255 is 110 and D255 is 110, don't count F255. or if D255 is 110 and L254 is 110 dont count D255)

    This would be greatly appreciated.

    thank you~!

    jeb
    Attached Files Attached Files
    Last edited by myjebay1; 05-17-2013 at 03:20 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: count the number of times a number appears in a specific range

    Not sure VBA is needed...though this formula approach can be turned into a macro, certainly, just by recording these steps one time:

    1) Turn on macro recorder

    2) Enter these values in N253:Q253
    110
    130
    150
    160

    3) Put this formula in N254, then copy it down and across through Q1454:

    =IF(OR($D254=N$253, $F254=N$253, $H254=N$253, J254=$N$253, L$254=N253),1,0)

    4) Repeat these values in N1456:Q1456
    110
    130
    150
    160

    5) Put this formula in N1457, then copy to the right:

    =SUM(N254:N1454)

    6) To make this result permanent, highlight N1457:Q1457, COPY, then Paste Special > Values.

    7) Now you can delete the data above in N253:Q1454

    8) Turn off the macro recorder. That macro should be reusable now.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ 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