I have 13 columns of data (i.e. 13 weeks) - and I want to count number of instances..

[mickyd67]

Hi All

I want to count the number of instances a number drops below zero.

However I want to be able to choose how many weeks the formula is calculating. For instance if I wanted to see all instances of a number dropping below zero I'd type 13 into an adjacent, to the formula, cell.

If I only wanted to see the number in this week I'd just type 1 into the cell, so it only looks at 1 column on data.

I am sure I did this before but I cannot find the formula I used. Please help

 

[Toadstool]

Hi MickyD67,

Does this satisfy your needs?

MickyD67.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13		Weeks	Count < zero
2	-4	-5	5	-4	-3	2	1	-1	-4	-1	4	5	-4		7	35
3	-2	0	1	-5	0	1	1	4	4	-5	0	0	0
4	-3	4	-3	-3	5	5	-5	-2	2	-2	5	-3	-5
5	-2	-1	0	3	-1	-4	2	1	1	2	1	-4	-2
6	1	-4	1	0	-4	-4	-4	-3	5	5	3	3	0
7	3	-2	2	1	3	2	-2	-1	-1	1	-1	2	0
8	4	-4	-1	-5	3	-2	-5	0	-4	5	4	-1	0
9	4	-2	-4	1	2	4	0	3	5	-1	-3	-4	-3
10	-4	3	2	-3	4	2	0	2	1	5	0	3	-4
11	2	3	-1	4	-1	5	3	4	4	0	-3	3	-4
12	2	-4	-3	-3	5	-2	4	-1	-4	3	2	-5	-4
Sheet1

Cell Formulas
Range	Formula
P2	P2	=SUMPRODUCT(--(A2:M12<0)*(COLUMN(A2:M12)<=O2))

Cells with Data Validation
Cell	Allow	Criteria
O2	List	1,2,3,4,5,6,7,8,9,10,11,12,13

 

[mickyd67]

This is great - thanks a lot for putting that effort in. This is a really handy formula.

One thing I am trying to also avoid, however, is double counting. In row 2 I'd say that is one instance as the same item goes below zero more than once. I am also aiming for unique instances (sorry if that wasn't clear :-/)

Thanks again.

 

[Toadstool]

You didn't specify double counting but as row 2 has 13 entries I also don't understand what the "same item" is? Unique per row? Unique per week?