Reduce Multiple COUNTIFs

[VisionSmart]

Hello all

Does anyone have a way to reduce the multiple COUNTIF function in the following to use one COUNTIF to encompass all ranges please?

=COUNTIF('Panel Draw'!B11:T40,"H1")+COUNTIF('Panel Draw'!B58:T87,"H1")+COUNTIF('Panel Draw'!B105:T134,"H1")+COUNTIF('Panel Draw'!B145:T174,"H1")+COUNTIF('Panel Draw'!B185:T223,"H1")

I need to use the statement addressing different cell ranges about 200 times in a Workbook.

Tanks

[Glenn Kennedy]

Will ther be any instances of H1 in between the specified ranges? If not, why not use:

=COUNTIF('Panel Draw'!B11:T223,"H1"), or even:

=COUNTIF('Panel Draw'!B:T,"H1")

[kvsrinivasamurthy]

Try this

=SUMPRODUCT(COUNTIF(OFFSET(B12:T41,(ROW($1:$3)-1)*47,0),"H1"))+SUMPRODUCT(COUNTIF(OFFSET(B146:T175,(ROW($1:$2)-1)*40,0),"H1"))

[VisionSmart]

Thanks Glenn

Yes there are several instances of the Hi in between the ranges, and not to be included in this statement. hence the specific ranges.
When I used your suggestion, HI was being included in calculations even though Hi was outside the specified range.
I also tried the Sum(Product) statement, and the sum(countif) but still very long.
Have not tried yet but I wonder if a named range would shorten the statement?

[kvsrinivasamurthy]

Try this, This works.

=SUMPRODUCT(COUNTIF(OFFSET('Panel Draw'!B11:T40,{0,47,94,134},0),"H1"))+COUNTIF('Panel Draw'!B185:T223,"H1")

[AliGW]

Why do you want to reduce the length of the formula? What is your perceived problem with it? Is it to do with the formula's performance, or merely an aesthetic whim? If the latter, then if it ain't broke, you don't need to fix it.

[Glenn Kennedy]

The solution at post 5 only works if the ranges are all the same size. they aren't.

here's another option that will work with irregularly sized blocks.

I have used "my own" ranges for ease of demonstration:

=SUM(COUNTIF(INDIRECT({"A1:B5","A12:B16","A19:B27"}),"H1"))

or, using named ranges:

=SUM(COUNTIF(INDIRECT({"rnga","rngb","rngc"}),"H1"))

[VisionSmart]

Thank you Glenn. Your Indirect part ensures I only count data within the range and does shorten the whole formula.
I was getting calculations including data outside the cell ranges (such as headings and cell addresses containing H111 etc.)
Attached is an example of my workbook which is designed to gather data on Football players.
I want to place the formulas in a separate tab which I can password and hide.

The part I have not been able to figure out is how to include the Panel Draw Tab name in the formula. Have tried many variations but no success.
This link is indicating a 3rd Reference is not allowed. If this is the case, is there another solution please?
https://www.pcreview.co.uk/threads/s...heets.3770785/

[Glenn Kennedy]

see amendments. However.... if the only reason that you are splitting the dataset is to make it look pretty when you print it - it can be done MUCH more simply. So, why do you have 3 separate sections?

[VisionSmart]

Thankyou Glenn. I am a novice with EXCEL so appreciate your amendments - I would never achieved that formula.