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

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: 

Originally Posted by teylyn

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

Sorry
Thank you!

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!