How to look through non-contiguous cell arrays using COUNTIF

[djk957]

I want to create a formula that will look through several cell arrays to determine if a cell (one or more) have a value greater than 0. If so, the return a true condition.

Here is the formula I have tried

=IF(COUNTIF(TST_RANGE,">0"),1,0) (doesn't work)

TST_RANGE is a named cell range and is defined as
=Chart1!$L$5:$L$9,Chart1!$N$5:$N$9

The formula is operating on cell arrays that are not contiguous.

The formula works OK if TST_RANGE is defined as with contiguous columns.

My data is in non-contiguous columns so I need to scan the first array, skip several columns, scan the next array, skip columns, so on and so forth.

 

[wigi]

Why don't you simply take the sum? And check whether that's bigger than 0 or not.

=--(SUM(TST_RANGE)>0)

 

[cgcamal]

Hi djk,

An option would be this array formula, not sure if apply for the solution you need:

Excel Workbook
	K	L	M	N
4	Result	Range1=L5:L9		Range2=N5:N9
5	1	-2		-1
6		-1		0
7		0.1		-1
8		-1		-4
9		-1		-2
...

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Excel Workbook
Name	Refers To
Range1	=Sheet1!$L$5:$L$9
Range2	=Sheet1!$N$5:$N$9
Workbook Defined Names

Hope this helps.

Regards.

 

[T. Valko]

I want to create a formula that will look through several cell arrays to determine if a cell (one or more) have a value greater than 0. If so, the return a true condition.

Here is the formula I have tried

=IF(COUNTIF(TST_RANGE,">0"),1,0) (doesn't work)

TST_RANGE is a named cell range and is defined as
=Chart1!$L$5:$L$9,Chart1!$N$5:$N$9

The formula is operating on cell arrays that are not contiguous.

The formula works OK if TST_RANGE is defined as with contiguous columns.

My data is in non-contiguous columns so I need to scan the first array, skip several columns, scan the next array, skip columns, so on and so forth.

Try this...

For a return of 1 or 0...

=--(INDEX(FREQUENCY(TST_RANGE,0),2)>0)

 

[markmzz]

Djk957

Note1: it isn't the most smart formula, but I think it can help you.

Note2: this formula can work if the name myName have until 12 areas in it. I think that the formula can do more (it have only 907 characters and, if I'm not wrong, you can use until 32,000), do some tests.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Col01</td><td style="text-align: center;;">Col02</td><td style="text-align: center;;">Col03</td><td style="text-align: center;;"></td><td style="text-align: center;;">Total >0</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=COUNTIF(<font color="Blue">INDEX(<font color="Red">myName,,,1</font>),">0"</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,2</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,2</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,3</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,3</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,4</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,4</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,5</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,5</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,6</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,6</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,7</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,7</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,8</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,8</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,9</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,9</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,10</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,10</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,11</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,11</font>),">0"</font>)</font>)+IF(<font color="Blue">ISERR(<font color="Red">COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,12</font>),">0"</font>)</font>),0,COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,12</font>),">0"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">0,3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">0,3</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myName</th><td style="text-align:left">=Sheet4!$A$2:$A$6,Sheet4!$C$2:$C$6</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[wigi]

I could also suggest the MAX function instead of my earlier SUM function suggestion, but that's about the most complicated you should make it. Contrary to the other suggestions in the topic, of which I do not see any benefit rather than a theoretical one.

=--(MAX(TST_RANGE)>0)

MAX could be used if if could happen that - for the SUM variant - strictly negative values could undo strictly positive values.

 

[markmzz]

Sorry about my previous. I didn't had understood correctly what you wanted. Now I think this formula can help you:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Col01</td><td style="text-align: center;;">Col02</td><td style="text-align: center;;">Col03</td><td style="text-align: center;;"></td><td style="text-align: center;;">Exist >0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">INDEX(<font color="Green">myName,,,1</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>1,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,2</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>2,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,3</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>3,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,4</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>4,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,5</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>5,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,6</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>6,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,7</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>7,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,8</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>8,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,9</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>9,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,10</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>10,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,11</font>),</font>),</font>)+IF(<font color="Red">AREAS(<font color="Green">myName</font>)>11,COUNTIF(<font color="Green">INDEX(<font color="Purple">myName,,,12</font>),</font>),</font>)>0,1,</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">0,9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">0,9</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myName</th><td style="text-align:left">=Sheet4!$A$2:$A$6,Sheet4!$C$2:$C$6</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[mortgageman]

I could also suggest the MAX function instead of my earlier SUM function suggestion, but that's about the most complicated you should make it. Contrary to the other suggestions in the topic, of which I do not see any benefit rather than a theoretical one.

=--(MAX(TST_RANGE)>0)

MAX could be used if if could happen that - for the SUM variant - strictly negative values could undo strictly positive values.

Since I had a similar question, I am posting this link - perhaps some of the ideas here will also be useful to you.


http://www.mrexcel.com/forum/showthread.php?t=568196

Gene Klein

 

[markmzz]

I want to create a formula that will look through several cell arrays to determine if a cell (one or more) have a value greater than 0. If so, the return a true condition.

Here is the formula I have tried

=IF(COUNTIF(TST_RANGE,">0"),1,0) (doesn't work)

TST_RANGE is a named cell range and is defined as
=Chart1!$L$5:$L$9,Chart1!$N$5:$N$9

The formula is operating on cell arrays that are not contiguous.

The formula works OK if TST_RANGE is defined as with contiguous columns.

My data is in non-contiguous columns so I need to scan the first array, skip several columns, scan the next array, skip columns, so on and so forth.

Sorry, my formula had a error again. Try this:

=IF(COUNTIF(INDEX(TST_RANGE,,,1),">0")+IF(AREAS(TST_RANGE)>1,COUNTIF(INDEX(TST_RANGE,,,2),">0"),)+IF(AREAS(TST_RANGE)>2,COUNTIF(INDEX(TST_RANGE,,,3),">0"),)+IF(AREAS(TST_RANGE)>3,COUNTIF(INDEX(TST_RANGE,,,4),">0"),)+IF(AREAS(TST_RANGE)>4,COUNTIF(INDEX(TST_RANGE,,,5),">0"),)+IF(AREAS(TST_RANGE)>5,COUNTIF(INDEX(TST_RANGE,,,6),">0"),)+IF(AREAS(TST_RANGE)>6,COUNTIF(INDEX(TST_RANGE,,,7),">0"),)+IF(AREAS(TST_RANGE)>7,COUNTIF(INDEX(TST_RANGE,,,8),">0"),)+IF(AREAS(TST_RANGE)>8,COUNTIF(INDEX(TST_RANGE,,,9),">0"),)+IF(AREAS(TST_RANGE)>9,COUNTIF(INDEX(TST_RANGE,,,10),">0"),)+IF(AREAS(TST_RANGE)>10,COUNTIF(INDEX(TST_RANGE,,,11),">0"),)+IF(AREAS(TST_RANGE)>11,COUNTIF(INDEX(TST_RANGE,,,12),">0"),)>0,1,0)

Markmzz

 

[T. Valko]

I could also suggest the MAX function instead of my earlier SUM function suggestion, but that's about the most complicated you should make it. Contrary to the other suggestions in the topic, of which I do not see any benefit rather than a theoretical one.

=--(MAX(TST_RANGE)>0)

MAX could be used if if could happen that - for the SUM variant - strictly negative values could undo strictly positive values.

That's too bad.

You might find the INDEX(FREQUENCY formula is a very useful and efficient formula.

For example, suppose you want to get a count of numbers >0 in the same range across 10 sheets.

=INDEX(FREQUENCY(Sheet1:Sheet10!A1:B10,0),2)

Plenty of benefit if you ask me!