Excel - Sumif only in the first line of a date

[AttAtt]

hey all ,


im new here


i need to make sumif by date but only in the first line of the date .
if there is duplicate date dont make sum if just insert 0.
in the pic you can see in column C the result that i need

date	number <tbody> </tbody>	result <tbody> </tbody>
1/2/2018 1/2/2018 1/3/2018 1/3/2018 1/4/2018 1/4/2018 1/5/2018 1/5/2018 1/5/2018 1/17/2018 1/17/2018 1/19/2018 1/19/2018 1/19/2018 <colgroup><col></colgroup><tbody> </tbody>	0 73.48 0 139.48 0 -153.12 0 -115.78 -99.18 0 240.68 0 169.6866 231.4534 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	73.48 0 139.48 0 -153.12 0 -214.96 0 0 240.68 0 401.14 0 0 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[admiral100]

Hi,

Try this :

C2 =IF(B2=0,SUMIF($A$2:$A$15,A2,$B$2:$B$15),0)

	A	B	C
1	Date	Number	Result
2	1/2/2018	0	73.48
3	1/2/2018	73.48	0
4	1/3/2018	0	139.48
5	1/3/2018	139.48	0
6	1/4/2018	0	-153.12
7	1/4/2018	-153.12	0
8	1/5/2018	0	-214.96
9	1/5/2018	-115.78	0
10	1/5/2018	-99.18	0
11	1/17/2018	0	240.68
12	1/17/2018	240.68	0
13	1/19/2018	0	401.14
14	1/19/2018	169.6866	0
15	1/19/2018	231.4534	0

<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[AttAtt]

Hi,

Try this :

C2 =IF(B2=0,SUMIF($A$2:$A$15,A2,$B$2:$B$15),0)

	A	B	C
1	Date	Number	Result
2	1/2/2018	0	73.48
3	1/2/2018	73.48	0
4	1/3/2018	0	139.48
5	1/3/2018	139.48	0
6	1/4/2018	0	-153.12
7	1/4/2018	-153.12	0
8	1/5/2018	0	-214.96
9	1/5/2018	-115.78	0
10	1/5/2018	-99.18	0
11	1/17/2018	0	240.68
12	1/17/2018	240.68	0
13	1/19/2018	0	401.14
14	1/19/2018	169.6866	0
15	1/19/2018	231.4534	0

<tbody>
</tbody>

Hey ,
thanks for the answer ,
there is a problem in those lines :

8/30/2018 8/30/2018 8/30/2018 <colgroup><col></colgroup><tbody> </tbody>

0 0 -49.8 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

-49.8 -49.8 0 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

in column C i expected to get -49.8 only in the first row

Thanks

 

[admiral100]

Try this : (leave the header in column C empty)

C2 =IF(AND(B2=0,C1=0),SUMIF($A$2:$A$18,A2,$B$2:$B$18),0)

	A	B	C
1	Date	Number
2	01/02/18	0	73.48
3	01/02/18	73.48	0
4	01/03/18	0	139.48
5	01/03/18	139.48	0
6	01/04/18	0	-153.12
7	01/04/18	-153.12	0
8	01/05/18	0	-214.96
9	01/05/18	-115.78	0
10	01/05/18	-99.18	0
11	1/17/2018	0	240.68
12	1/17/2018	240.68	0
13	1/19/2018	0	401.14
14	1/19/2018	169.6866	0
15	1/19/2018	231.4534	0
16	8/30/2018	0	-49.8
17	8/30/2018	0	0
18	8/30/2018	-49.8	0

<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[jtakw]

Hi,

This will do what you want regardless of how many dates are 0 (zero) before there's a number in Column B, no need to remove Headers:

Book1
	A	B	C
1	date	number	result
2	1/2/2018	0	73.48
3	1/2/2018	73.48	0
4	1/3/2018	0	139.48
5	1/3/2018	139.48	0
6	1/4/2018	0	-153.12
7	1/4/2018	-153.12	0
8	1/5/2018	0	-214.96
9	1/5/2018	-115.78	0
10	1/5/2018	-99.18	0
11	1/17/2018	0	240.68
12	1/17/2018	240.68	0
13	1/19/2018	0	401.14
14	1/19/2018	169.6866	0
15	1/19/2018	231.4534	0
16	8/30/2018	0	-49.8
17	8/30/2018	0	0
18	8/30/2018	0	0
19	8/30/2018	-49.8	0
Sheet262

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A$2:A$19,A2,B$2:B$19),0)

Formula copied down.

 

[AttAtt]

Hi,

This will do what you want regardless of how many dates are 0 (zero) before there's a number in Column B, no need to remove Headers:

	A	B	C
1	date	number	result
2	1/2/2018	0	73.48
3	1/2/2018	73.48	0
4	1/3/2018	0	139.48
5	1/3/2018	139.48	0
6	1/4/2018	0	-153.12
7	1/4/2018	-153.12	0
8	1/5/2018	0	-214.96
9	1/5/2018	-115.78	0
10	1/5/2018	-99.18	0
11	1/17/2018	0	240.68
12	1/17/2018	240.68	0
13	1/19/2018	0	401.14
14	1/19/2018	169.6866	0
15	1/19/2018	231.4534	0
16	8/30/2018	0	-49.8
17	8/30/2018	0	0
18	8/30/2018	0	0
19	8/30/2018	-49.8	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet262​

Worksheet Formulas Cell Formula C2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A$2:A$19,A2,B$2:B$19),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formula copied down.

Thanks alot ! its Work !

 

[jtakw]

You're welcome.