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:

Please Login or Register  to view this content.

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....

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Thanks for all your help!

Counting cells in a row that are less than 0

LinkBack

Thread Tools

Rate This Thread

Display