Counting cells in a row that are less than 0

**Scorpvin** · 09-09-2008, 02:44 PM

I need to count the largest string of cells that contains negative numbers in consecutive cells. See attached file. I hard coded column P.

**mrice** · 09-09-2008, 02:53 PM

No attachment?

**Ron Coderre** · 09-09-2008, 03:25 PM

Using your posted workbook, this ARRAY FORMULA (committed with
CTRL+SHIFT+ENTER, instead of just ENTER) seems to work:

Q1: =IF(COUNT(1/((A1:N1<0)*(B1:O1<0))),MAX(FREQUENCY(
IF((A1:N1<0)*(B1:O1<0),COLUMN(A1:N1)),IF((A1:N1<0)*(B1:O1>=0),
COLUMN(A1:N1))))+1,0)

Copy Q1 and paste into Q2 and down as far as you need.

Does that help?

**Scorpvin** · 09-09-2008, 03:40 PM

It worked! Thank you!
Same sample, new problem.
If column A2 is negative I need to count the consecutive negative cells from A2 on. The result for Q2 is 4. For Q1 it would be 0 because A1 is positive.

**Ron Coderre** · 09-09-2008, 03:46 PM

Perhaps the same ARRAY FORMULA with a small tweak at the beginning....

Q1: =IF((A1<0)*COUNT(1/((A1:N1<0)*(B1:O1<0))),MAX(FREQUENCY(
IF((A1:N1<0)*(B1:O1<0),COLUMN(A1:N1)),IF((A1:N1<0)*(B1:O1>=0),
COLUMN(A1:N1))))+1,0)

Copy Q1 and paste into Q2 and down as far as you need.

Does that help?

**Scorpvin** · 09-09-2008, 04:08 PM

Thanks Ron. You're awesome! Have a blessed day!

**Scorpvin** · 09-09-2008, 04:32 PM

If A1 is negative I need to count how many cells including are negative consecutive cells including A1. See attached.

**Ron Coderre** · 09-09-2008, 04:41 PM

Maybe this....regular formula:

Q1: =IF(AND(A1<0,COUNTIF(A1:O1,">=0")),MATCH(0,INDEX((A1:N1<0)*(B1:O1<0),0),0),0)

Does that work for you?

**Scorpvin** · 09-09-2008, 04:58 PM

Worked for row 1 and row 2. Unsuccessful for row 3.

**shg** · 09-09-2008, 06:09 PM

Maybe =IF(A1 >= 0, 0, IF(AND(A1:O1 < 0), COUNT(A1:O1), MATCH(TRUE, A1:O1 > 0, 0) - 1) )

**Scorpvin** · 09-10-2008, 12:37 PM

Still need some help.

**NBVC** · 09-10-2008, 12:44 PM

Perhaps:

=IF(COUNTIF(A1:O1,"<0")=COUNT(A1:O1),COUNT(A1:O1),IF(A1<0,MATCH(0,INDEX((A1:N1<0)*(B1:O1<0),0),0),0))

**Scorpvin** · 09-10-2008, 01:00 PM

Thanks for all your help!

Thread: Counting cells in a row that are less than 0

Thread Tools

Display