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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]result[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1/2/2018[/TD]
[/TR]
[TR]
[TD]1/2/2018[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[/TR]
[TR]
[TD]1/17/2018[/TD]
[/TR]
[TR]
[TD]1/17/2018[/TD]
[/TR]
[TR]
[TD]1/19/2018[/TD]
[/TR]
[TR]
[TD]1/19/2018[/TD]
[/TR]
[TR]
[TD]1/19/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]73.48[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]139.48[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]-153.12[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]-115.78[/TD]
[/TR]
[TR]
[TD="class: xl65"]-99.18[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]240.68[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]169.6866[/TD]
[/TR]
[TR]
[TD="class: xl65"]231.4534[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]73.48[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]139.48[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]-153.12[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]-214.96[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]240.68[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]401.14[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this :

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



[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Date[/TD]
[TD="class: xl63"]Number[/TD]
[TD="class: xl63"]Result[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl64"] 1/2/2018
[/TD]
[TD="class: xl63"]
0[/TD]
[TD="class: xl63"]73.48[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"]1/2/2018[/TD]
[TD="class: xl63"]73.48[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl64"]1/3/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]139.48[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1/3/2018[/TD]
[TD="class: xl63"]139.48[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl64"]1/4/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]-153.12[/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl64"]1/4/2018[/TD]
[TD="class: xl63"]-153.12[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl64"]1/5/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]-214.96[/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl64"]1/5/2018[/TD]
[TD="class: xl63"]-115.78[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl64"]1/5/2018[/TD]
[TD="class: xl63"]-99.18[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]11[/TD]
[TD="class: xl64"]1/17/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]240.68[/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl64"]1/17/2018[/TD]
[TD="class: xl63"]240.68[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]13[/TD]
[TD="class: xl64"]1/19/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]401.14[/TD]
[/TR]
[TR]
[TD="class: xl63"]14[/TD]
[TD="class: xl64"]1/19/2018[/TD]
[TD="class: xl63"]169.6866[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]15[/TD]
[TD="class: xl64"]1/19/2018[/TD]
[TD="class: xl63"]231.4534[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[AttAtt]

Hi,

Try this :

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



[TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Date[/TD]
[TD="class: xl63"]Number[/TD]
[TD="class: xl63"]Result[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl64"] 1/2/2018[/TD]
[TD="class: xl63"] 0[/TD]
[TD="class: xl63"]73.48[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"]1/2/2018[/TD]
[TD="class: xl63"]73.48[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl64"]1/3/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]139.48[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1/3/2018[/TD]
[TD="class: xl63"]139.48[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl64"]1/4/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]-153.12[/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl64"]1/4/2018[/TD]
[TD="class: xl63"]-153.12[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl64"]1/5/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]-214.96[/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl64"]1/5/2018[/TD]
[TD="class: xl63"]-115.78[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl64"]1/5/2018[/TD]
[TD="class: xl63"]-99.18[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]11[/TD]
[TD="class: xl64"]1/17/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]240.68[/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl64"]1/17/2018[/TD]
[TD="class: xl63"]240.68[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]13[/TD]
[TD="class: xl64"]1/19/2018[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]401.14[/TD]
[/TR]
[TR]
[TD="class: xl63"]14[/TD]
[TD="class: xl64"]1/19/2018[/TD]
[TD="class: xl63"]169.6866[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]15[/TD]
[TD="class: xl64"]1/19/2018[/TD]
[TD="class: xl63"]231.4534[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 132"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]8/30/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8/30/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8/30/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]-49.8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]-49.8[/TD]
[/TR]
[TR]
[TD="align: right"]-49.8[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 70"][/TD]
[TD="class: xl66, width: 70"]A[/TD]
[TD="class: xl66, width: 70"]B[/TD]
[TD="class: xl66, width: 70"]C[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Date[/TD]
[TD="class: xl66"]Number[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl65, align: right"]01/02/18[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]73.48[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl65, align: right"]01/02/18[/TD]
[TD="class: xl66"]73.48[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl65, align: right"]01/03/18[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]139.48[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl65, align: right"]01/03/18[/TD]
[TD="class: xl66"]139.48[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl65, align: right"]01/04/18[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]-153.12[/TD]
[/TR]
[TR]
[TD="class: xl66"]7[/TD]
[TD="class: xl65, align: right"]01/04/18[/TD]
[TD="class: xl66"]-153.12[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]8[/TD]
[TD="class: xl65, align: right"]01/05/18[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]-214.96[/TD]
[/TR]
[TR]
[TD="class: xl66"]9[/TD]
[TD="class: xl65, align: right"]01/05/18[/TD]
[TD="class: xl66"]-115.78[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]10[/TD]
[TD="class: xl65, align: right"]01/05/18[/TD]
[TD="class: xl66"]-99.18[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]11[/TD]
[TD="align: left"]1/17/2018[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]240.68[/TD]
[/TR]
[TR]
[TD="class: xl66"]12[/TD]
[TD="align: left"]1/17/2018[/TD]
[TD="class: xl66"]240.68[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]13[/TD]
[TD="align: left"]1/19/2018[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]401.14[/TD]
[/TR]
[TR]
[TD="class: xl66"]14[/TD]
[TD="align: left"]1/19/2018[/TD]
[TD="class: xl66"]169.6866[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]15[/TD]
[TD="align: left"]1/19/2018[/TD]
[TD="class: xl66"]231.4534[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]16[/TD]
[TD="align: left"] 8/30/2018
[/TD]
[TD="class: xl66"]
0[/TD]
[TD="class: xl66"]-49.8[/TD]
[/TR]
[TR]
[TD="class: xl66"]17[/TD]
[TD="align: left"]8/30/2018[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]18[/TD]
[TD="align: left"]8/30/2018[/TD]
[TD="class: xl66"]-49.8 [/TD]
[TD="class: xl66"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
date	number	result

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]73.48[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]73.48[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]139.48[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]139.48[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-153.12[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]-153.12[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-214.96[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]-115.78[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]-99.18[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1/17/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]240.68[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/17/2018[/TD]
[TD="align: right"]240.68[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1/19/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]401.14[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1/19/2018[/TD]
[TD="align: right"]169.6866[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1/19/2018[/TD]
[TD="align: right"]231.4534[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]8/30/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-49.8[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]8/30/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]8/30/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]8/30/2018[/TD]
[TD="align: right"]-49.8[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet262​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A$2:A$19,A2,B$2:B$19),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

Thanks alot ! its Work !

 

[jtakw]

You're welcome.