Using formulas within SUMIFS

Question

I have a three column workbook with the following data:

Col A: Names
Col B: Numbers
Col C: Dates

I'm currently using the following formula to get the sum of numbers from e.g. all "John" within the month of January.

=SUMIFS(B:B; A:A; "John"; C:C; ">="&DATE(2012;1;1); C:C; "<"&DATE(2012;2;1))

Is there a more elegant way of finding the correct month.

I did try:

=SUMIFS(B:B; A:A;"John"; MONTH(C:C);1))

But that did not work.

Answer 1

Try this:

=SUMPRODUCT(--((A1:A100)="John"),B1:B100,--(MONTH(C1:C100)=1))

Answer 2

not sure about the elegancy thing, but you can add a new column month using this formula (format the number as mm if you like):

=DATE(YEAR(C2); MONTH(C2); 1)

or =MONTH(C2) to return the same number for different years

and to get the result:

=SUMIFS(B:B; A:A; "John"; D:D; "2012-01-01")