Sum cells based on date criteria

trydon · Apr 5th 2013

Hello I have been having a huge problem tryign to get Sumif to work correctly.

Here is my data below Columns P is date and time and column Q is Actual Qty.

[TABLE="width: 233"]

[tr]

[td]

Time

[/td]

[td]

Actual

[/td]

[/tr]

[/TABLE]

[TABLE="width: 233"]

[tr]

[td]

05/04/2013 14:40

[/td]

[TD="align: right"]96[/TD]

[/tr]

[tr]

[td]

05/04/2013 14:50

[/td]

[TD="align: right"]96[/TD]

[/tr]

[tr]

[td]

05/04/2013 15:00

[/td]

[TD="align: right"]64[/TD]

[/tr]

[tr]

[td]

05/04/2013 15:10

[/td]

[TD="align: right"]32[/TD]

[/tr]

[tr]

[td]

05/04/2013 15:20

[/td]

[TD="align: right"]128[/TD]

[/tr]

[tr]

[td]

05/04/2013 15:30

[/td]

[TD="align: right"]96[/TD]

[/tr]

[tr]

[td]

05/04/2013 15:40

[/td]

[TD="align: right"]128[/TD]

[/tr]

[tr]

[td]

05/04/2013 15:50

[/td]

[TD="align: right"]96[/TD]

[/tr]

[tr]

[td]

05/04/2013 16:00

[/td]

[TD="align: right"]64[/TD]

[/tr]

[tr]

[td]

05/04/2013 16:10

[/td]

[TD="align: right"]32[/TD]

[/tr]

[tr]

[td]

05/04/2013 16:20

[/td]

[TD="align: right"]32[/TD]

[/tr]

[tr]

[td]

05/04/2013 16:30

[/td]

[TD="align: right"]96[/TD]

[/tr]

[tr]

[td]

05/04/2013 16:40

[/td]

[TD="align: right"]128[/TD]

[/tr]

[tr]

[td]

05/04/2013 16:50

[/td]

[TD="align: right"]96[/TD]

[/tr]

[tr]

[td]

05/04/2013 17:00

[/td]

[TD="align: right"]64[/TD]

[/tr]

[tr]

[td]

05/04/2013 17:10

[/td]

[TD="align: right"]64[/TD]

[/tr]

[tr]

[td]

05/04/2013 17:20

[/td]

[TD="align: right"]128[/TD]

[/tr]

[tr]

[td]

05/04/2013 17:30

[/td]

[TD="align: right"]128[/TD]

[/tr]

[tr]

[td]

05/04/2013 17:40

[/td]

[TD="align: right"]32

[/TD]

[/tr]

[/TABLE]
Here is my sum if statment to count the quality within each hour. (i have many of these for 24 hours)

=SUMIF(Data!P:P,"05/04/****13:**",Data!Q:Q)

Im not sure why this does not work, if i type the date and tiem in in full it works. but wildcards do not work. Im confused why.
Hope you can help!

etaf · Apr 5th 2013

Re: Sumif wildcard help! (DAte/time)

not sure about the wild card
but you could try
=SUMIFS(Data!Q:Q, Data!P:P ,">=5/4/13 00:00", Data!P:P ,"<=5/4/13 23:59:59")

OR

=SUMPRODUCT(--(Data!P:P>DATE(2013,4,4)),--(Data!P:P<DATE(2013,4,6)),Data!Q:Q)

trydon · Apr 5th 2013

Re: Sumif wildcard help! (DAte/time)

Quote from etaf;658187

not sure about the wild card
but you could try
=SUMIFS(Data!Q:Q, Data!P:P ,">=5/4/13 00:00", Data!P:P ,"<=5/4/13 23:59:59")

Sorry but this comes up with an error. (because of SUMIFS not SUMIF). but when i change it to SUMIF i still get a formattign error. not sure you can put ,"<=5/4/13 23:59:59")

etaf · Apr 5th 2013

Re: Sumif wildcard help! (DAte/time)

SUMIFS works and that text work - tried on a sheet before posting

see attached

what version of excel - can you use SUMIFS ?

trydon · Apr 5th 2013

Re: Sumif wildcard help! (DAte/time)

Sorry i use 2003, and sumifs does not work. any other solution?
is it possable to recreat this in VBA?

lamking · Apr 5th 2013

Re: Sumif wildcard help! (DAte/time)

Hi,

I find that SUMPRODUCT is very easy to use in these sorts of cases . . . you could use the following:

=SUMPRODUCT((HOUR(P$2:P$20)=14)*(Q$2:Q$20))
=SUMPRODUCT((HOUR(P$2:P$20)=15)*(Q$2:Q$20))
=SUMPRODUCT((HOUR(P$2:P$20)=16)*(Q$2:Q$20))
=SUMPRODUCT((HOUR(P$2:P$20)=17)*(Q$2:Q$20))

I personally would also have defined dynamic ranges for the date / time and quantity.

Hope this helps.

Ian

etaf · Apr 5th 2013

Re: Sumif wildcard help! (DAte/time)

=SUMPRODUCT(--(Data!P:P>DATE(2013,4,4)),--(Data!P:P<DATE(2013,4,6)),Data!Q:Q)

i edited for sumproduct - but lamking
also replied

added a 2003 version

2 rows have 4/4/13 and 6/4/13 in so number is not 1600

trydon · Apr 6th 2013

Re: Sumif wildcard help! (DAte/time)

Thank you I will try this later

Sum cells based on date criteria

Participate now!

Share