+ Reply to Thread
Results 1 to 6 of 6

Counting value across several ranges

  1. #1
    Registered User
    Join Date
    03-21-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2011 & 2010
    Posts
    52

    Counting value across several ranges

    Hello All,

    I am drawing a blank for a simple formula to count the letter "A" across multiple ranges. I would like to count how many instances the letter "A" exists in ranges A1:a100, c1:c100, e1:e100,h1:h100 and so on. At the moment, the only formula that works for me is "=COUNTIF(A1:A100, "R")+COUNTIF(C1:C100,"R")+...).

    Any other suggestions? Perhaps an array formula?


    Thanks in advance!!

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Counting value across several ranges

    Hello,

    will there be "A"s that you don't want to count in columns B, D, E, etc? If not, you can use one range A1:H100 in the formula.

    Is there a pattern to the spread of A values? It seems to start in every other column, but then it changes. If A values are only in columns A, C, E, G, etc. i.e. columns with an odd column number, you could use

    =SUMPRODUCT(--(A1:H20="A")*(ISODD(COLUMN(A1:H1))))

    If that does not do the trick, please provide a data sample and some more explanation.

    cheers, teylyn

  3. #3
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2003 | 2010
    Posts
    365

    Re: Counting value across several ranges

    Quote Originally Posted by NU2vba View Post
    Hello All,

    I am drawing a blank for a simple formula to count the letter "A" across multiple ranges. I would like to count how many instances the letter "A" exists in ranges A1:a100, c1:c100, e1:e100,h1:h100 and so on. At the moment, the only formula that works for me is "=COUNTIF(A1:A100, "R")+COUNTIF(C1:C100,"R")+...).
    You try
    HTML Code: 
    Sub CharacreCountA1()
        Dim iR As Long
        Dim jC As Long
        Dim LetterCount1 As Long
        Dim LetterCount2 As Long
        For iR = 1 To 100
            For jC = 1 To 5 Step 2
                If Cells(iR, jC) = "A" Then
                    LetterCount1 = LetterCount1 + 1
                End If
            Next
            For jC = 6 To 8 Step 2
                If Cells(iR, jC) = "A" Then
                    LetterCount2 = LetterCount2 + 1
                End If
            Next
    
        Next
        [J1] = LetterCount1 + LetterCount2
    End Sub
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Counting value across several ranges

    tax112, The OP is asking for a simple formula, not VBA. This is the fomulas forum.

  5. #5
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2003 | 2010
    Posts
    365

    Re: Counting value across several ranges

    Quote Originally Posted by teylyn View Post
    tax112, The OP is asking for a simple formula, not VBA. This is the fomulas forum.
    Sorry
    Thank you!

  6. #6
    Registered User
    Join Date
    03-21-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2011 & 2010
    Posts
    52

    Re: Counting value across several ranges

    Quote Originally Posted by teylyn View Post
    Hello,

    will there be "A"s that you don't want to count in columns B, D, E, etc? If not, you can use one range A1:H100 in the formula.

    Is there a pattern to the spread of A values? It seems to start in every other column, but then it changes. If A values are only in columns A, C, E, G, etc. i.e. columns with an odd column number, you could use

    =SUMPRODUCT(--(A1:H20="A")*(ISODD(COLUMN(A1:H1))))

    If that does not do the trick, please provide a data sample and some more explanation.

    cheers, teylyn
    Hi teylyn,

    WOW! That works! I've never heard of ISODD and didn't think to find a pattern with columns since data is 80 columns deep. I will definitely end up using ISEVEN for the other columns.

    Thanks a bunch!

+ 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. Counting in between ranges
    By dreicer_Jarr in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-13-2014, 12:54 AM
  2. [SOLVED] Counting Matching values in two separate ranges without counting duplicates
    By Rhall6310 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2014, 03:35 PM
  3. Counting value ranges
    By mljs54 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-13-2013, 03:21 PM
  4. Counting with ranges
    By chemmiah in forum Excel General
    Replies: 3
    Last Post: 10-19-2011, 11:39 AM
  5. [SOLVED] Counting variable ranges and auto-summing variable ranges
    By Father Guido in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2006, 11:10 PM

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