Excel 2007 : Using SUMIFS to calculate cells based on a specific month

Hello Members,

I am seeking assistance with the following formula.

I wish to run a formula that totals the values in column B on "sheet 2" based on the month of a particular date in a cell on "sheet 1".

For example if Cell A1 on Sheet 1 was the 13/01/2012, I want the formula to total all values in column B on sheet 2 where column A is the same month.

A B
6/01/2012 20.00
07/01/2012 10.00
13/01/2012 20.00
13/02/2012
14/03/2012

I want this formula to read from the date cell on sheet 1, so this formula does not have to be manually updated.

Any assistance with this is greatly appreciated.

JASB

You need SUMPRODUCT...

=SUMPRODUCT(--(MONTH(A1:A500)=MONTH(Sheet1!A1)),B1:B500)

Although, unless all of your dates are in the same year you probably want to expand it to:

=SUMPRODUCT(--(MONTH(A1:A500)=MONTH(Sheet1!A1)),--(YEAR(A1:A500)=YEAR(Sheet1!A1)),B1:B500)

Andrew,

Thanks for the prompt reply.

This was exactly what I was after.

Richard, thanks also.

JASB