Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Sumif conditions
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Sumif conditions
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")=TE XT( 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 -- Jacob "Paul" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Sumif conditions
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 -- Biff Microsoft Excel MVP "Paul" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with multiple conditions | Excel Discussion (Misc queries) | |||
Multiple Sumif conditions | Excel Worksheet Functions | |||
sumif with multiple conditions | Excel Worksheet Functions | |||
SumIF-multiple conditions/OR | New Users to Excel | |||
SUMIF with Multiple Conditions | Excel Worksheet Functions |