Returning totals based on values in another column

[nightracer]

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

 

[eduzs]

Try this:

=SUMIFS(C1:C13,A1:A13,DATEVALUE("13/3/2017"),C1:C13,"<0")

Change:

C1:C13 the range with the values
A1:A13 the range with the dates
DATEVALUE("13/3/2017") the date you want to get the total

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Date	Tot	N			1​	2​	3​
2​	3/12/2017​	2​	1.94​		3/12/2017​	0​	0​	0​
3​	3/12/2017​	3​	2.91​		3/13/2017​	-10.8​	-19.2​	0​
4​	3/12/2017​	2​	1.94​		3/14/2017​	-22.8​	0​	0​
5​	3/12/2017​	1​	0.97​		3/15/2017​	0​	0​	-7.2​
6​	3/12/2017​	2​	1.94​
7​	3/12/2017​	3​	2.91​
8​	3/13/2017​	3​	2.91​
9​	3/13/2017​	1​	0.97​
10​	3/13/2017​	1​	0.97​
11​	3/13/2017​	1​	0.97​
12​	3/13/2017​	2​	1.94​
13​	3/13/2017​	2​	1.94​
14​	3/13/2017​	2​	-19.2​
15​	3/13/2017​	1​	0.97​
16​	3/13/2017​	2​	1.94​
17​	3/13/2017​	1​	-10.8​
18​	3/14/2017​	1​	0.97​
19​	3/14/2017​	2​	1.94​
20​	3/14/2017​	1​	-10.8​
21​	3/14/2017​	1​	0.97​
22​	3/14/2017​	1​	0.97​
23​	3/14/2017​	1​	0.97​
24​	3/14/2017​	1​	0.97​
25​	3/14/2017​	1​	-12​
26​	3/15/2017​	1​	0.97​
27​	3/15/2017​	3​	-7.2​
28​	3/15/2017​	2​	1.94​
29​	3/15/2017​	1​	0.97​
30​	3/15/2017​	2​	1.94​
31​	3/15/2017​	2​	1.94​
32​	3/15/2017​	1​	0.97​
33​	3/15/2017​	2​	1.94​
34​	3/15/2017​	1​	0.97​

In F1 enter, copy across, and down:

=SUMIFS($C:$C,$C:$C,"<0",$A:$A,$E2,$B:$B,F$1)

 

[nightracer]

Many thanks eduzs for such a quick reply.

Is there a way to link the DATEVALUE to a cell value to find the date to look for?

ie can I have a column for date and have the formula refer to that in a summary table?

Many thanks again

 

[nightracer]

Thanks Aladin, it is the total of Col B for each day where Col C is negative that I am looking to return, rather than the negative values themselves.

 

[Aladin Akyurek]

Thanks Aladin, it is the total of Col B for each day where Col C is negative that I am looking to return, rather than the negative values themselves.

Row\Col	A​	B​	C​	D​	E​	F​
1​	Date	Tot	N
2​	3/12/2017​	2​	1.94​		3/12/2017​	0​
3​	3/12/2017​	3​	2.91​		3/13/2017​	3​
4​	3/12/2017​	2​	1.94​		3/14/2017​	2​
5​	3/12/2017​	1​	0.97​		3/15/2017​	3​
6​	3/12/2017​	2​	1.94​
7​	3/12/2017​	3​	2.91​
8​	3/13/2017​	3​	2.91​

In F2 enter and copy down:

=SUMIFS(B:B,A:A,E2,C:C,"<0")

 

[nightracer]

Superb, thank you so much Aladin (and eduzs), that will save me a lot of time.

Thanks again
Nightracer