Counting the number of occurances with consecutive values above zero

**bryan3228** · 03-06-2015, 03:37 PM

I am trying to count the number of instances where an item has received orders in consecutive months.

In column P I have hardcoded the answers but I want to find a formula that will do this for me

Book3.xlsx

**samba_ravi** · 03-06-2015, 03:48 PM

=SUM((FREQUENCY(IF(B2:M2>0,COLUMN(B2:M2)),IF(B2:M2<=0,COLUMN(B2:M2)))>0)*1)
try this array formula(Shift+Ctrl+Enter) and copy towards down

**JieJenn** · 03-06-2015, 03:57 PM

If your report gets too big, UDF might be a better option since it doesn't require as much memory as array formula.

Please Login or Register  to view this content.

**bryan3228** · 03-13-2015, 09:35 AM

Unfortunately that didn't quite work. If an item was ordered for one month, it is counting that in the total. One month shouldn't be calculated. An item would need to be ordered for two months or more