+ Reply to Thread
Results 1 to 8 of 8

sumifs with dates not working

  1. #1
    Registered User
    Join Date
    10-30-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    sumifs with dates not working

    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

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sumifs with dates not working

    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?
    Audere est facere

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,228

    Re: sumifs with dates not working

    Look at my attachment.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-30-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: sumifs with dates not working

    Quote Originally Posted by daddylonglegs View Post
    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.

  5. #5
    Registered User
    Join Date
    10-30-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: sumifs with dates not working

    Quote Originally Posted by alansidman View Post
    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

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sumifs with dates not working

    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

  7. #7
    Registered User
    Join Date
    10-30-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: sumifs with dates not working

    Quote Originally Posted by daddylonglegs View Post
    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.

  8. #8
    Registered User
    Join Date
    10-30-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: sumifs with dates not working

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1