Sum If on Dates

[rhmkrmi]

Hello,

I am trying to do a sum if and my criteria is a date, e.g. column A contains dates in the format dd/mm/yyyy and I have the sum value in column B.

I am not sure how I can do a sum if on a date criteria?

Thanks for your assistance in advance.

 

[Weazel]

There's really quite a few ways to do it, I think it really depends on how you have your data set up to determine which type of formula to use

heres a few, though my preference is #2 with the date in a separate cell

Excel 2012

	A	B	C	D
=SUMIF($A$2:$A$10,DATE(2014,1,1),$B$2)
=SUMIF($A$2:$A$10,"="&B1,B2:B10)
=SUMIF(A2:A10,"=41640",$B$2)
=SUMIF(A2:A10,"=1/1/2014",B2:B10)
=SUMPRODUCT(--(A2:A10=41640),B2:B10)

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/2/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/2/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/2/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/2/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1/2/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[rhmkrmi]

Thank you so much.

I was thinking there might be a tirck, for example, using the * in sum if or something similar but thank you very much anyways.

 

[PaddyD]

or use a pivot table...