# Counting value across several ranges

1. ## 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?

2. ## 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. ## Re: Counting value across several ranges

Originally Posted by NU2vba
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``````

4. ## Re: Counting value across several ranges

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

5. ## Re: Counting value across several ranges

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

6. ## Re: Counting value across several ranges

Originally Posted by teylyn
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!

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

#### 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