Multiple Sumif conditions

[Paul]

I have a list of data by date of power production. What I want to know is
how much power was generated in December 2009, January 2010, etc. The data in
a sheet with the dates in column A and power production in column B. I have
another tab with the months in the rows and the years on the columns.

I am sure there is a way to do this with sumproduct, but i am not sure how to.

Thanks in advance.

 

[Jacob Skaria]

Suppose you have data in Shee1 ColA with dates and Sheet2 with power
production. In Sheet2 arrange months and year as below and try the below
formula in cell B2 and copy down/across as required

=SUMPRODUCT((TEXT(Sheet1!$A$2:$A$1000,"mmyyyy")=TEXT(
DATEVALUE("1-"&$A2&"-"&B$1),"mmyyyy"))*Sheet1!$B$2:$B$1000)


Col A Col B Col C Col D
2007 2008 2009
Jan 0 0 0
Feb 0 0 8
Mar 0 0 0
Apr 0 0 0
May 0 0 0
Jun 0 0 0
Jul 0 0 0
Aug 0 0 10

 

[T. Valko]

Try this...

Sheet1:

A2:A20 = dates
B2:B20 = numbers to sum

Sheet2:

A2:A13 = month names as TEXT entries in the form mmm: Jan, Feb, Mar, Apr,
etc.
B1:C1 = year numbers = 2009, 2010

Enter this formula in B2 Sheet2:

=SUMPRODUCT(--(TEXT(Sheet1!$A$2:$A$20,"yyyymmm")=B$1&$A2),Sheet1!$B$2:$B$20)

Copy across to C2 then down to B13:C13