sumifs with dates not working

**paualou76** · 10-30-2011, 03:33 PM

Hi,

I'm trying to calculate totals, per month, dependent on two criteria, (1) date (2) type of expense and am running into problems. I'm sure it must be something simple but I've been staring at it for too long now and I think I'm going cross-eyed.

A sample of my data (3 columns, 8 rows) is as follows:

E F G
01-Apr-11 paypal fees 1.71
02-Apr-11 paypal fees 5.18
02-Apr-11 refund - postage 0.60
03-Apr-11 paypal fees 1.19
04-Apr-11 paypal fees 1.58
04-Apr-11 postage 3.44
05-Apr-11 paypal fees 1.70
05-Apr-11 postage 10.81

I would like to find out the sum of column G when the column E contains 'Apr' and column F contains 'paypal'. I type the date in as '01 Apr' and the cells are set to custom dd-mmm-yy.

Ideally I would like to just have the two criteria and match E with 'Apr' but I couldn't get this to work so then I tried to set a 3 criteria formula (1) start date 01-Apr-11 (2) end date 05-Apr-11 (3) contains paypal.

One formulae I tried was:

=SUMIFS('2011-2012 purchases'!$H5:$H$65536,'Apr2011-Mar2012 accounts'!E5:E65536,">="&'Apr2011-Mar2012 accounts'!E5,'Apr2011-Mar2012 accounts'!E5:E65536,"<="&'Apr2011-Mar2012 accounts'!E54,'Apr2011-Mar2012 accounts'!F5:F65536,"paypal*")

but I got the result of zero. I always seem to be getting zero.

Can anyone help me?

Many thanks
Paula

**daddylonglegs** · 10-30-2011, 03:58 PM

Hello Paula,

You said that your sum range was column G but in the formula you are summing column H on a different sheet, i.e. '2011-2012 purchases'!$H5:$H$65536. Is that right?

**alansidman** · 10-30-2011, 04:09 PM

Look at my attachment.

**paualou76** · 10-30-2011, 05:06 PM

Originally Posted by daddylonglegs

Hello Paula,

You said that your sum range was column G but in the formula you are summing column H on a different sheet, i.e. '2011-2012 purchases'!$H5:$H$65536. Is that right?

Sorry, that wa a typo. The example given should have column H instead of column G as there is a hidden column in the actual worksheet.

**paualou76** · 10-30-2011, 05:21 PM

Originally Posted by alansidman

Look at my attachment.

Hi Alan,

Thanks for that. I can see what you have done, and this is how I would have expected to construct the formula myself but when I apply the formula to my entire datasheet I'm still getting a value of zero. Do you think this could be because there are some empty rows between the months? Each month has a continuous number of rows but there is a gap between months.

The equation I have tried, based on you example, is:

=SUMIFS('2011-2012 purchases'!H5:H65536,'Apr2011-Mar2012 accounts'!E5:E65536,">3/31/11",'Apr2011-Mar2012 accounts'!E5:E65536,"<5/1/11",'Apr2011-Mar2012 accounts'!F5:F65536,"=paypal fees")

I have tried with both American and British style of dates as the date cell formats are dd-mmm-yy.

Paula

**daddylonglegs** · 10-30-2011, 07:52 PM

Your original formula was valid. If it doesn't work then either you don't have valid dates or the sum range doesn't contain numbers - can I check again, the sum range is on a different worksheet from the other data - that seems a little odd.....?

It would be preferable, I believe, to keep dates in cells for the criteria as you had originally - if you use criteria like ">3/31/11" and "<5/1/11" then those will be interpreted as per your regional settings - if you are in England then presumably your regional settings use dd/mm/yy dates so the first one won't be recognised as a valid date and the second one will be treated as 5th January.

I recommend you use a small sample (100 rows) to check that your formula works and check each criterion separately with COUNTIF formulas on the same sheet as the data. For example what do you get with

=COUNTIF(E1:E100,">"&F1)

where F1 contains a date

**paualou76** · 10-30-2011, 08:24 PM

Originally Posted by daddylonglegs

Your original formula was valid. If it doesn't work then either you don't have valid dates or the sum range doesn't contain numbers - can I check again, the sum range is on a different worksheet from the other data - that seems a little odd.....?

Sorry, that was a mistake. The corrected verson (all on the same worksheet) of the formula works but I still have to find the two dates in my worksheet that I'm using to be able to enter the cell numbers. I would prefer the alternative way, where I can just enter the dates or better yet, get the formula to recognise any cells CONTAINING 'apr' to work. When I try the date method I'm still getting a zero.

**paualou76** · 10-30-2011, 08:33 PM

I've finally sorted it using the first and last day of the month. It would have helped if I had put the correct last day of April as the 30th rather than the 31st. Sorry, it's been a long day I should have noticed these little mistakes. Thanks for all your help everyone.