Skip navigation

How do I get SUMIF to calculate based on multiple variables?

609 Views 5 Replies Latest reply: May 15, 2012 8:19 PM by schmiford RSS
schmiford Calculating status...
Currently Being Moderated
May 7, 2012 8:01 PM

Here's what I have now


=SUMIF(Income Breakdown :: $Date,D1,Income Breakdown :: $Amount)


Income Breakdown is the table I'm referring to. D1 is March, so if the cell in the Date column = March it will figure the total for me of just those items.

However I want it to figure the total by both the Month and by the Category (a separate column in the table {but separate row in the table I'm trying to create the formula in}) I have listed in the table. How do I include variables for both month and category and get a sum based on that?

Just a side note, I do not work with spreadsheets often. I looked through other discussions, but as none were quite my problem or my variables, I had trouble following them. I stumbled on this formula in a template and knew it should do what I want, I just cannot solve that 2nd variable.

 

I tried doing this:

=SUMIF(Income Breakdown :: $Date,D1,Income Breakdown :: $Category,A3,Income Breakdown :: $Amount)

This causes all the required columns to highlight in color, but it gives me a red triangle once I hit enter.

iWork Numbers, iOS 5.1
  • Barry Level 7 Level 7 (29,095 points)

    Hi,

     

    You're almost there.

     

    SUMIF calculates a sum of values that meet a single condition.

     

    SUMIFS calculates a sum of values that meet all of a set of conditions.

     

    The syntax is slightly different. Here's the description, from the Function Browser in Numbers '09:

    Picture 6.png

     

    Note that the sum values (Income Breakdown::$Amount) is the first argument for SUMIFS, rather than the last, as it was for SUMIF. Test-value, Condition pairs are set in the same manner as in SUMIF.

     

    Regards,

    Barry

  • Badunit Level 6 Level 6 (10,760 points)

    Numbers '08?  I don't have that version to check it out but maybe it doesn't have SUMIFS.  Version '09 has it.

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

    Badunit,

     

    You're right, SUMIFS didn't exist in Numbers '08.

     

    You can get around this problem by adding a column that combines the two conditions, then SUMIF with the new column as a single condition.

     

    For instance, if you want to sum all the values associated with a particular color and size, in columns C and D, in Column E fill with =C&D. This will give you values like RedSmall and BlueMedium. Do a SUMIF using column E as the condition range and look for RedSmall, or whatever.

     

    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.