nightracer
Board Regular
- Joined
- May 17, 2004
- Messages
- 147
- Office Version
- 365
Hi
I'm trying to return the totals of a column based on negative values in another column.
I think a SUMPRODUCT may be the answer but cant get it to work.
As below, I have date, no of entries and value in columns A B & C.
I want to return the total of column B for a given date where the values in column C are negative.
So I want to return:
12.3.17 0 (no negative values in Col C)
13.3.17 3 (1 (at -19.2) + 2 (at -10.8)
14.3.17 2 (1 (at -10.8) + 2 (at -12.0)
15.3.17 3 (at -7.2)
The number of entries on each day is variable.
A B C
Date Tot N
12.3.17 2 1.94
12.3.17 3 2.91
12.3.17 2 1.94
12.3.17 1 0.97
12.3.17 2 1.94
12.3.17 3 2.91
13.3.17 3 2.91
13.3.17 1 0.97
13.3.17 1 0.97
13.3.17 1 0.97
13.3.17 2 1.94
13.3.17 2 1.94
13.3.17 2 -19.20
13.3.17 1 0.97
13.3.17 2 1.94
13.3.17 1 -10.80
14.3.17 1 0.97
14.3.17 2 1.94
14.3.17 1 -10.80
14.3.17 1 0.97
14.3.17 1 0.97
14.3.17 1 0.97
14.3.17 1 0.97
14.3.17 1 -12.00
15.3.17 1 0.97
15.3.17 3 -7.20
15.3.17 2 1.94
15.3.17 1 0.97
15.3.17 2 1.94
15.3.17 2 1.94
15.3.17 1 0.97
15.3.17 2 1.94
15.3.17 1 0.97
Any help much appreciated
I'm trying to return the totals of a column based on negative values in another column.
I think a SUMPRODUCT may be the answer but cant get it to work.
As below, I have date, no of entries and value in columns A B & C.
I want to return the total of column B for a given date where the values in column C are negative.
So I want to return:
12.3.17 0 (no negative values in Col C)
13.3.17 3 (1 (at -19.2) + 2 (at -10.8)
14.3.17 2 (1 (at -10.8) + 2 (at -12.0)
15.3.17 3 (at -7.2)
The number of entries on each day is variable.
A B C
Date Tot N
12.3.17 2 1.94
12.3.17 3 2.91
12.3.17 2 1.94
12.3.17 1 0.97
12.3.17 2 1.94
12.3.17 3 2.91
13.3.17 3 2.91
13.3.17 1 0.97
13.3.17 1 0.97
13.3.17 1 0.97
13.3.17 2 1.94
13.3.17 2 1.94
13.3.17 2 -19.20
13.3.17 1 0.97
13.3.17 2 1.94
13.3.17 1 -10.80
14.3.17 1 0.97
14.3.17 2 1.94
14.3.17 1 -10.80
14.3.17 1 0.97
14.3.17 1 0.97
14.3.17 1 0.97
14.3.17 1 0.97
14.3.17 1 -12.00
15.3.17 1 0.97
15.3.17 3 -7.20
15.3.17 2 1.94
15.3.17 1 0.97
15.3.17 2 1.94
15.3.17 2 1.94
15.3.17 1 0.97
15.3.17 2 1.94
15.3.17 1 0.97
Any help much appreciated