2 Replies Latest reply: May 3, 2012 12:46 AM by Barry
Jamesgarside10 Level 1 Level 1 (0 points)

Hi, I have a checking account in numbers '09 (columns as below)

 

Type

Date

Description

Category

Amount

Balance

 

 

I need a formula to calculate the amount spent in a certain category within a given time period.

I have used the formula: =SUMIF($Category,A2,Transactions :: Amount) to get the total amount for each category but my records go back 6 years so need to break it down into years/months.

 

I have looked around but cant seem to find a solution to what I assumed to be a simple problem?

 

Many thanks


Numbers '09, Mac OS X (10.7.3)
  • 1. Re: Numbers formula
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    James,

     

    Numbers '09 has SUMIFS in which you can set numerous conditions such as Category, Before this Date, After That date, etc.

     

    Regards,

     

    Jerry

  • 2. Re: Numbers formula
    Barry Level 7 Level 7 (29,180 points)

    Hi James,

     

    Jerry has correctly identified the function you need.

     

    One detail that may be important: Both SUMIF and SUMIFS assume "=" as the comparison when it is not specified. For your case, where you want to include entries between two dates, you will need to set conditions that use > or < as the comparison operators in the condition, and you will need to construct each condition as a text string.

     

    Example:

    Picture 38.png

    Here, the cells in column A contain Date and Time values, set to display only the Date part in the format shown. These were entered as dates, so the Time part has been set by numbers to 00:00:00 (midnight, at the beginning of the date shown).

     

    The cells in column C also contain Date and Time values. Each is the first day of the month and year displayed, and like the ones in column A, the time part is 00:00:00. The cells have been formatted to display only the Month name and year, as shown.

     

    Column B contains amounts, chosen to make apparent where the totals in column D are coming from.

     

    There is one formula on the table, entered in D2, and filled down from there (to D4):

     

    =SUMIFS(B,A,"<="&EOMONTH(C2,0),A,">"&EOMONTH(C2,-1))

     

    Syntax and descriptions for the two functions used, SUMIFS and EOMONTH, may be found in the iWork Formulas and Functions User Guide. The guide may be downloaded via the Help menu in Numbers '08.

     

    Regards,

    Barry