Skip navigation

Error nesting MONTH function inside SUMIFS - help!

421 Views 4 Replies Latest reply: Jan 17, 2013 5:42 AM by Jerrold Green1 RSS
Sirolway Calculating status...
Currently Being Moderated
Jan 14, 2013 12:49 AM

Hi all

 

I'm trying to sum amounts by month & category using SUMIFS:

 

 

A. Gives the error: "SUMIFS requires that all rage arguments are the same size"

=SUMIFS(January 2013 :: Amount,MONTH(January 2013 :: Date),1,January 2013 :: Category,A2)

 

 

B. Works

If I add a 'Month No' column to the source table defined as MONTH(January 2013), I can use that successfully in my expression - i.e. this works:

=SUMIFS(January 2013 :: Amount,Month No,1,January 2013 :: Category,A2)

 

 

 

 

I really don't want to use (B) - not least because I'll have to maintain a hidden column, but also because there's a more elegant solution almost within reach...

 

So it seems like SUMIFS can't handle a nested 'MONTH' function in the middle of it, which severely limits its usefulness.

Can anyone help me get (a) working?

MacBook Pro (13-inch Mid 2009), OS X Mountain Lion (10.8.2)
  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jan 14, 2013 4:07 AM (in response to Sirolway)

    Sirolway,

     

    The reason your first scheme fails is that the sub-expression "MONTH(January 2013 :: Date)" returns a Value, whereas the function SUMIFS is looking for a a cell range Address specification. While I admit to not having checked, I suppose that all the compatible spreadsheets out there would have the same issue with your approach.

     

    The alternative is to have an additional condition pair in your Sumifs expression. Specify that the date of the records be Greater than or equal to the first day of the month in question AND less than the first day of the next month. Specifying the end of the range is what takes the additional condition.

     

    You can calculate the first day of the next month with the expression: EOMONTH(date) + 1, where date is any day in the current month.

     

    Regards,

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,185 points)

    Correction -

     

    The expression that I wrote about, EOMONTH, should have included the Month Offset argument. I assume that you looked up the syntax before using, but I should have written:

     

    You can calculate the first day of the next month with the expression: EOMONTH(date, 0) + 1, where date is any day in the current month.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jan 17, 2013 5:42 AM (in response to Sirolway)

    You can get function help from within Numbers by using View > Show Function Browser.

     

    Good luck,

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.