If Column A = (certain month) Add (Column C)

If column A contains the names of the months and not actual date & time values that are formatted to show only the names of the months,

=SUMIF(A,"april",C)

It could be the same as looking for APRIL by substituting the full date. Better though, with dates, is to put the date you want to use in a particular cell and then reference that cell in the formula.

This eliminates problems with date formatting.

Jerry

Instead of: =SUMIF(A,"april",C) , use: =SUMIF(A,"Apr 12, 2010",C)

Better though would be to type the date of interest into, say, Table 2 :: A1 and then the formula would be: =SUMIF(A, Table 2 :: D1, C)

Jerry

Replying to myself here to correct the last expression in my previous post. Should be: =SUMIF(A, Table 2 :: A1, C)

Jerry

itsalexclark wrote:
How do I do this same thing if instead of it listed as a month I have a date entered.

for example:
instead of APRIL
I have Apr 12, 2010

How can I extract the month and the find the sum of the amounts?

Assuming you want to sum all of the amounts in April, you'll need to extract the Month (number) from each date, then use the SUMIF function suggested by Jerry to sum the amounts.



If all your dates are in the same year, or if you want to include all of the amounts from April (eg.) without regard to the year, SUMIF will do the job correctly. If you have dates in more than one year, and are interested in the monthly sums for only one year at a time, you'll also need to extract the Year from the date and use SUMIFS.

Formulas:
Columns A and B on both tables are entered directly. Column B on the Summary table is not necessary to the calculations. Columns D and E on the Data table may be hidden.

Data table:
Column D: =MONTH(A)
Column E: =YEAR(A)

Summary table:

Column C (C2): =SUMIF(Data :: 'Month #',"=1",AMT)

Numbers automatically changes the "A" and "B" references in the formula to the names in the column headers, and specifies the table for the first (Month #) as there are columns with this name on both tables.
Note that the April sum in this column includes the three amounts in 2010 and the three amounts in 2011.

Column D (D2): =SUMIFS(AMT,Data :: 'Month #',"=1",Year,"=2010")

Note that the sums in this column include only the amounts where the date is in 2010.

For either of these formulas, the "=1" comparison must be changed manually for each row to match the Month number for the appropriate month.

For the second formula, change the second comparison to the appropriate year.

The quotation marks enclosing the comparison expression are necessary.

Regards,
Barry

Unfortunately that formula will not work. MONTH(A) will not result in an array of the months in the entire column, it will return a single value that is the month from column A in the same row as the formula.

Here is a formula that does not require the "month" column and also has no problem with localized date formats. This example will add up the values for January 2010.

EDIT: The parser for this forum uses the less than and greater than symbols for other things so you often cannot post them as part of formulas. I am replacing them in the formulas with the words, you will have to change them back to symbols

=SUMIFS(C,A,"greater than or equal to"&DATE(2010,1,1),A,"less than"&DATE(2010,2,1))

Here is another where you would put the first of the month (e.g., 1/1/2010) in a cell (I used column F for no reason other than it was available in my table) and the formula references that cell for the date.

=SUMIFS(C,A,"greater than or equal to"&F,A,"less than or equal to"&EOMONTH(F,0))


Message was edited by: Badunit

Message was edited by: Badunit

Hello Badunit

a clever tip to get rid of problems with the messages parser is to post a screenshot of the formula.

For you, it would be easy to apply it.

I can't do that because I work in French and if I run Numbers in English, it uses the decimal comma and the semi-colon as separator.

An other efficient tip is to encapsulate the formula between the AppleScript balises



--

=SUMIFS(C,A,"≥"&DATE(2010,1,1),A,"l<n"&DATE(2010,2,1))
--

In the AppleScript display, it's not perfectly legible but we may apply copy-paste to use it.

Yvan KOENIG (VALLAURIS, France) mardi 6 avril 2010 23:54:00

--

<= => <sds> < >
level 1
 level 2
--

It won't accept "not equal to" unless I put an option-space between the two symbols.
Nice that it will maintain line indentations
With that new knowledge under my belt, I think I'll stop with the thread hijack

also new to numbers and been trying to get a variation of this formula to work for me with not much luck. either i get a syntax error (depending on the presence of quotation marks) or an incorrect response.

this version gives me a sum of 0, which isn't correct.
=SUMIFS(paid,category,A2,date,">=&D1",date,"<=&EOMONTH(D1,0)")

would really appreciate some help.

I didn't read back through the rest of this thread but I can spot what appears to be an error in your formula.

It should probably read like this:
=SUMIFS(paid,category,A2,date,">="&D1,date,"<="&EOMONTH(D1,0))

I moved the quotes

thanks - that looks better, but i'm still getting the incorrect sum.
is there a specific way that the dates in the 'date' column need to be formatted?
i've tried changing the formatting and writing dates as =DATE(2009,03,01) but still keep getting a zero sum.