Non-contiguous named range
 

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

Non-contiguous named range
 

Not your problem. COUNTIF works on contiguous ranges only.

The usual workaround is to add up the contiguous pieces:

=COUNTIF() + COUNTIF() + etc.
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

Non-contiguous named range
 

Thanks
Is there VB code I could use instead as there are quite a few ranges.

"Gary''s Student" wrote:

Not your problem. COUNTIF works on contiguous ranges only.

The usual workaround is to add up the contiguous pieces:

=COUNTIF() + COUNTIF() + etc.
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

Non-contiguous named range
 

Sub iamthecount()
For Each r In Range("SOURCE")
If r.Value < "ABCD" Then
i = i + 1
End If
Next
MsgBox (i)
End Sub

will count the cells that do not equal ABCD
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

Thanks
Is there VB code I could use instead as there are quite a few ranges.

"Gary''s Student" wrote:

Not your problem. COUNTIF works on contiguous ranges only.

The usual workaround is to add up the contiguous pieces:

=COUNTIF() + COUNTIF() + etc.
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

Non-contiguous named range
 

Thanks again. That works.

"Gary''s Student" wrote:

Sub iamthecount()
For Each r In Range("SOURCE")
If r.Value < "ABCD" Then
i = i + 1
End If
Next
MsgBox (i)
End Sub

will count the cells that do not equal ABCD
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

Thanks
Is there VB code I could use instead as there are quite a few ranges.

"Gary''s Student" wrote:

Not your problem. COUNTIF works on contiguous ranges only.

The usual workaround is to add up the contiguous pieces:

=COUNTIF() + COUNTIF() + etc.
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

Non-contiguous named range
 

Have a good weekend.
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

Thanks again. That works.

"Gary''s Student" wrote:

Sub iamthecount()
For Each r In Range("SOURCE")
If r.Value < "ABCD" Then
i = i + 1
End If
Next
MsgBox (i)
End Sub

will count the cells that do not equal ABCD
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

Thanks
Is there VB code I could use instead as there are quite a few ranges.

"Gary''s Student" wrote:

Not your problem. COUNTIF works on contiguous ranges only.

The usual workaround is to add up the contiguous pieces:

=COUNTIF() + COUNTIF() + etc.
--
Gary''s Student - gsnu200756


"JoeMNY" wrote:

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

Non-contiguous named range
 

There are a couple of other ways to skin this cat

you can use this


=SUM(COUNTIF(INDIRECT({"'Log Sheet'!$O$61:$O$110","'Log
Sheet'!$O$125:$O$174","'Log Sheet'!$O$189:$O$238","'Log
Sheet'!$O$253:$O$302","'Log Sheet'!$O$317:$O$366","'Log
Sheet'!$O$381:$O$430"}),"<ABCD"))



or since you have a system where your data intervals are the same, 50 cells
then a gap of 14 cell then another 50 you can use the whole range


=SUMPRODUCT(--((MOD(ROW('Log Sheet'!$O$61:$O$430),64)<47)+(MOD(ROW('Log
Sheet'!$O$61:$O$430),65)60)0),--('Log Sheet'!$O$61:$O$430<"ABCD"))


and if you call the full range 'Log Sheet'!$O$61:$O$430 for SOURCE you can
use


=SUMPRODUCT(--((MOD(ROW(SOURCE),64)<47)+(MOD(ROW(SOURCE),65)60) 0),--(SOURCE<"ABCD"))




--


Regards,


Peo Sjoblom






"JoeMNY" wrote in message
...
I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log
Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"<ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe