8 Replies Latest reply: Feb 17, 2013 7:19 PM by Badunit
Dave6882 Level 1 Level 1 (0 points)

I have a table with running dates and totals. So in column A are dates, column B contains amounts. In another table I have months, January through December in column A and I want each months totals in column B. I used the following formula but it just returns a syntax error. =SUMIFS(Table 1 ::D29:D44),Table 1 ::A29:A44),=>"Feb 1, 2013",Table 1 ::A29:A44,=<"Feb 28, 2013")

 

A29:A44 is the column with the dates

D29:D44 is the column with the amounts if like to sum

 

So look at Column A and add all of the entries that fall into the date range I specify


iPad, iOS 5.1.1, Numbers v1.7.1
  • 1. Re: Sum values from a range of dates
    mkral Level 2 Level 2 (220 points)

    I haven't tried to recreate the entire spreadsheet, so there might be something else going on as well, but I can say for certain that you have too few ( relatvie to the number of )'s. It should be one to one &amp; right now you have 3 to 1. That's definitely triggering an error.

  • 2. Re: Sum values from a range of dates
    Badunit Level 6 Level 6 (10,815 points)

    You have a few too many )'s. You are not creating the condition strings correctly.

     

    You would be better off using the EOMONTH formula to get the last day of the month. It is more robust to use the DATE formula to create dates.

     

    =SUMIFS(Table 1 :: D29:D44,Table 1 :: A29:A44,">="&DATE(2013,2,1),Table 1 :: A29:A44,"<="&EOMONTH(DATE(2013,2,1),0))

     

    or

     

    =SUMIFS(Table 1 :: D29:D44,Table 1 :: A29:A44,">=feb 1, 2013",Table 1 :: A29:A44,"<="&EOMONTH("feb 1, 2013",0))

  • 3. Re: Sum values from a range of dates
    Dave6882 Level 1 Level 1 (0 points)

    I'm using numbers on my ipad so it looks much different (hard to tell where all the "()" are). I re-typed it so people could get the basic jist of what I was trying to do.

  • 4. Re: Sum values from a range of dates
    Badunit Level 6 Level 6 (10,815 points)

    Then maybe the only problem was the condition strings not being created correctly.

  • 5. Re: Sum values from a range of dates
    Dave6882 Level 1 Level 1 (0 points)

    I tried it again with no luck heres what it looks likeIMG_1003.PNG

  • 6. Re: Sum values from a range of dates
    Badunit Level 6 Level 6 (10,815 points)

    Kinda hard to read it but I don't see any quotes in the condition strings. Take another look at the formula I posted.  I'm not 100% sure how it looks when typed into the iOS version of Numbers.

  • 7. Re: Sum values from a range of dates
    Dave6882 Level 1 Level 1 (0 points)

    There aren't any. Actually I couldn't find and quote button on the iPad version of Numbers except where it inserts them automatically by entering plain text into a formula. This would seem like such an easy formula to do. I just don't get why it won't work.

  • 8. Re: Sum values from a range of dates
    Badunit Level 6 Level 6 (10,815 points)

    When entering a formula, there is a button labelled "abc" (with the quotes). Use it to enter text as part of a formula.

     

    Your formula does not work because it isn't constructed correctly yet. You'll get it.