Sumif and SumProduct

[littlefoot]

Hi,

I've a list of dates and basically i need to add up column B if Column A is between two dates.

ie i need to know totals for April through to March.

On the attachment
Column A is the date
Column B is a number

Column S has the start date
Colum T has the end date


Column U has a sumif formula (it seems to work but does not add up correctly for May, June August)
Column W has a sumproduct formula (it seems to work but does not add up correctly for May, June August)
Column V has a simple manual sum but is very labour intensive.

Please could someone help and let me know why my sumif and sumproduct does not work.

thanks in advance.

[daddylonglegs]

Your formulas would be OK if you had just dates in column A but you have times in there too so you have to amend a little to cope with those, try this version with SUMIFS for an accurate result

=SUMIFS(Sheet1!B:B,Sheet1!A:A,">="&S15,Sheet1!A:A,"<"&T15+1)

[littlefoot]

Daddylonglegs - this works fantanstic.

Thanks for everyones help.
x

Your formulas would be OK if you had just dates in column A but you have times in there too so you have to amend a little to cope with those, try this version with SUMIFS for an accurate result

=SUMIFS(Sheet1!B:B,Sheet1!A:A,">="&S15,Sheet1!A:A,"<"&T15+1)

[Pepe Le Mokko]

Your formula are ok, but you do not have any data between 01/04/2010 and 30/11/2010 ( and other intervals) - Therefore the formula rturns 0 as it should

[benishiryo]

hi Littlefoot. as dates are actually numbers, you'll realise some don't fit in the criteria because of time added in it. if you format 31-May-11 to General format for eg, you'll see it as 40694. but if you format A95, you'll see 40694.5913541667

that will cause your criteria to fail because you stated less than 31 May 2011 (i.e. 40694). you also dont need Column B. just do a COUNTIF
=COUNTIF(Sheet1!A:A,">="&S15)-COUNTIF(Sheet1!A:A,">"&T15+1)
=SUMPRODUCT(--(Sheet1!A:A>=S15),--(Sheet1!A:A<T15+1))