| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding last column in non-contiguous range | Excel Worksheet Functions | |||
| DSUM - non contiguous criteria range | Excel Worksheet Functions | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Excel copy formulas using non contiguous range | Excel Discussion (Misc queries) | |||
| SUMIF non-contiguous range | Excel Worksheet Functions | |||
Linear Mode
