Count range of cells if not next to each other

Hello all,

I am completely stuck with a formula in Excel 210 right now!

I'll give you a bit of background of what I am trying to do. I need to count the number of cells that are equal to '1' within ranges C175 & C177:C190 & C200:C280.

I have tried the formula below, however it doesn't work as the ranges are not together.

=COUNTIF('IDS BFR''s'!C175,'IDS BFR''s'!C177:C190,'IDS BFR''s'!C200:C280,"1")

Can anyone provide any assistance or provide a function that would work in this case?

Thanks very much

Originally Posted by vlady

is your "1" a real number. what is in cell 176,191 to 199 ?

Hi vlady,

Within the ranges in my formula the "1" can be either 1 2 or 3. But I only want it to count the cells that are = to 1.

The other ranges as you specified are for a different component and are not to be included in this count.

Hope you can help.

Thanks

Hi,

Its not actually the 'SUM' I am after. I need it to indicate how many cells have a 1 in it. I need to do the same for 2 & 3.

The range it needs to check is as follows:

C175 & C177:C190 & C200:C280.

Originally Posted by vlady

you can do that with the countif since it's just in the same column C

=countif(C175:c280,1) as long as c176,c191 to c199 will not have any number from 1 to 3

=countif(C175:c280,2)
=countif(C175:c280,3)

Hi Vlady,

Unfortunately c176,c191 to c199 do also have numbers from 1-3 and these cannot be counted for this equation.

I really need the to 'ranges' to be specific

Hi Ramananhrm,

That could work however, the range is split as indicated above so its not one complete range. Its actually 3 sections of a column.

Try this

Please Login or Register  to view this content.

Originally Posted by vlady

that's why i asked what are in those ranges in post #2

you can use

=sum(countif for first range),countif(secondrange),countif(3rd range))

Sorry Vlady I must'nt have explained myself well

However, the formula works perfect :D

I really appreciate it.

Thanks!!