# 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?   Register To Reply

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  Register To Reply

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``````  Register To Reply

4. ## Re: Counting value across several ranges

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

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!  Register To Reply

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!  Register To Reply