multiple ranges in countif formula

[Losguapos1]

So here is my dilemma:

I have a row of data one sheet and i want to count occurrences of the number "1" in all cells, but my problem is the range is not continuous.

Here is the formula i have started =COUNTIF('Grade K-8'!B4:Q4,"1,68") but i want to also include the ranges of U4:AJ4, AN4:BC4, BG4:BV4, AND BZ4:CO4

Any help would be greatly appreciated

George

[Tony Valko]

If there will not be any cells that contain "1,68" between the ranges then just do this:

=COUNTIF('Grade K-8'!B4:CO4,"1,68")

Otherwise, you can do this:

=COUNTIF('Grade K-8'!B4:Q4,"1,68")+COUNTIF('Grade K-8'!U4:AJ4,"1,68")+
COUNTIF('Grade K-8'!AN4:BC4,"1,68")+COUNTIF('Grade K-8'!BG4:BV4,"1,68")+COUNTIF('Grade K-8'!BZ4:CO4,"1,68")

[Losguapos1]

Thanks,

I was hoping to avoid that long of a furmula but i think there is no way around it

George

[Tony Valko]

This one is a few keystrokes shorter but uses the volatile function INDIRECT:

=SUM(COUNTIF(INDIRECT("'Grade K-8'!"&{"B4:Q4","U4:AJ4","AN4:BC4","BG4:BV4","BZ4:CO4"}),"1,68"))