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. 
0
Utf
12/18/2009 3:02:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
512 Views

Similar Articles

[PageSpeed] 2

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



-- 
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. 
0
Utf
12/18/2009 3:58:01 AM
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" <Paul@discussions.microsoft.com> wrote in message 
news:4DB01CFC-6B51-48FB-92C9-E7A25664E1BD@microsoft.com...
>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. 


0
T
12/18/2009 4:38:45 AM
Reply:

Similar Artilces:

1 word doc, multiple tabs
Other people have referenced a tabbed view to be able to view multiple documents at one time. Really Windows 7 taskbar takes care of this. I, on the other hand, want to see multiple tabs within 1 document like Excel. Example of use: I'm thinking about writing a book, and I have SEVERAL scenes in my head. Right now I would have to create SEVERAL word documents to get them all down, then create a main document and copy and paste stuff back and forth as I write. With a single tabbed document, I could write all my scenes as individual tabs, then "link" them to the m...

Multiple charts in ChartSpace; problems with double Categories
I want to show two or more charts with different categories and data in one chartspace, so I did the following: 1. Create Chartspace 2. Add Chart1 in Chartspace 3. Add Series in Chart1 4. Series.SetData chDimCategories 'A,B,C' 5. Series.SetData chDimValues '5,2,6' Now I see a chart with categories A (value=5), B (value=2) and C (value=6). So everything okay. Next thing I do: 6. Add Chart2 in Chartspace Now I see a second empty chart, BUT WITH already filled categories A,B and C!! I don't want this, because I want to use other categories in this second chart2. And whe...

Combine multiple records in one field in Access report
I'm having trouble designing a report. I have a report that sorts the data according to "Change Order Number", however, a change order can have multiple "Trend Numbers", "RFI Numbers", and other records associated with it and I want all of the "Trend Numbers" (and these other records) to fit on one line. For example, "Change Order Number 1" has 2 "Trend Numbers" associated with it, and the report currently shows this information in two rows with all of the other information for Change Order 1 repeating. I've done hide...

CRM Integration 1.2 for Great Plains
The CRM Integration 1.2 for Great Plains seems to only support integration between one Great Plains company databse and an installation of CRM. Does anyone know if it is possible to integrate more than one Great Plains database to a CRM installation using the the standard CRM Integration 1.2 for Great Plains and Biztalk server. CRM <=> GP Integration only supports integraiton to one GP company DB. >-----Original Message----- >The CRM Integration 1.2 for Great Plains seems to only support >integration between one Great Plains company databse and an >installation of CRM....

Accounts with multiple Relationship Types
Hi all! With some of our company' s Accounts we have multiple Relationship Types (eg. a Reseller can be a Customer too etc). How can we insert this information in Microsoft CRM 1.2? Is it necessary to create custom fields in the Account Form? Thanks a lot! Elena Galiatsou Athens, Greece You could choose a "primary" relationship type from the combo box, and then add a memo field to track additional relationship types. Not elegant, but I think it's the best you can do with version 1.0/1.2. Mike "SiEBEN Innovative Solutions" <newsgroup@sieben.gr> wrote i...

Conditional Formating (If statements)
In the conditional formatting box how would I make B4 turn yellow if F12 is between 10 and 15? Right-click Sheet, click 'View Code', and paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim rnArea As Range Dim rnCell As Range With rnCell If Range("F12").Value >= 10 And Range("F12").Value <= 15 Then Range("B4").Interior.ColorIndex = 6 Else: Range("B4").Interior.ColorIndex = 0 End If End With End Sub -- Ryan--- If this in...

Merging multiple excel files into a single doc
I've got about 40 excel documents. Each contains the same number of columns, and 25 lines worth of data. Rather then have 40 files, I'd like to have a single file with the data from each of the indvidual files all on this single file. Up until now, I've opened each doc, done a copy paste from the original doc into a master doc. This was ok for a while, but it appears this may be a regular thing I'll need to do. Is there any way to automate this process. The files will all be the same, in number of lines of data and number of columns. Thank you I like this way be...

EXCEL
Is it possible to put more than 3 conditional formats? I want to put around 8... If 0-5 blue, If 6-10 red, If 11 - 15 brown etc.... Thanks.... No, I believe 3 is the maximum. >-----Original Message----- >Is it possible to put more than 3 conditional formats? >I want to put around 8... If 0-5 blue, If 6-10 red, If >11 - 15 brown etc.... >Thanks.... >. > You'll find that you can't add more than 3 conditions. i.e. the answer to the OP's question is No. You would have to use an event macro (maybe On_Calculate) to scan the cells and set the colors if ...

Icon sets - conditional formatting
How do I get Excel 2003 to do this? I want to be able to add increase and decrease arrow icons to a column of numbers . Thx Upgrade to Excel 2007 or see these products; http://www.ozgrid.com/excel-add-ins/mini-charts-excel.htm http://www.ozgrid.com/excel-add-ins/spark-maker.htm -- Regards Dave Hawley www.ozgrid.com "Lou" <Lou@discussions.microsoft.com> wrote in message news:37AE35EE-B3C4-46B2-98F2-8A0B9627E12D@microsoft.com... > How do I get Excel 2003 to do this? > > I want to be able to add increase and decrease arrow icons to a colum...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

import multiple identites from outlook express to outlook 2003
How do you import multiple Outlook Express identities (mail and settings) into Outlook 2003? After installing Outlook 2003, it only imports the files from the main Outlook Express identity. Outlook does not use identities so it will not recognize them. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After searching google.groups.com and finding no answer mhat <anonymous@discussions.microsoft.com> asked: | How do you import multiple Outlook Exp...

multiple Personal Folders #2
I have a standalone win 2000 pro system using Outlook 2000 and I'm getting multiple Personal Folders for a user profile. How can I delete them and point the profile to a pst file on the disk? Thanks. Have you already tried right-clicking the root folder of each file (in the Folder List) and clicking Close? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:O37$8%23%230DHA.4064@tk2msftngp13.phx.gbl, SAC wrote: > I have a standalone win 2000 pro system us...

COUNTIF across multiple sheets
I have a workbook that has five sheets - September. . . January. I have a range of cells (D6:D17) where I need to count the occurrence of specific text from all sheets . For example: In D6:D17 sheet September has four cells with "fries", two with "malt" and two with "shrimp", the balance of cells in the range are empty. In D6:D17 sheet November has four cells with "malt" and all other range cells are empty On a sheet named Total I want to total the occurrence of "fries", "shrimp" and "malt" on all sheets in the workbook. ...

Cell extraction from Multiple worksheets
Hi group, I am new to this newsgroup and am hoping that you can help me out. I took a software/hardware inventory of all the computers in my work's network and imported them all into excel, in their own worksheet. So I have one file, with 128 different worksheets, all containing similar information. What I would like to do is create a new sheet, a summary page, where I can pull the value of the same cell from each sheet and list them. I need to have a list of each computer, with computer name, cpu speed, video card, serial number, ect. If the values that I am searching for are in the same...

Sumifs with data and dates
I need a formula for each item in col A that was added during 2010 only. the spreadsheet will have years of data. A B Expense Date Amount Furniture 1/12/2010 $326.00 Inventory 1/15/2010 $250.00 Utilities 1/15/2010 $98.00 Rent 1/29/2010 $1,000.00 Rent 1/30/2010 $536.00 how can I Sumif with Critera in Col A and only for a specific year? Gary Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight, WPF, XAML and InnerWorkings Coding Challenge http://www.eggheadcafe.com/tutorials/aspnet/f...

Wait Activity Condition
We have an activity that is added to the lead record automatically at the time the lead is created. It is a phone call activity for our reps to followup with the lead. We also have a sales process (workflow) set up for once the lead is converted to an opportunity. My question is: How can I keep the lead from being converted to an opportunity until the phone call activity on the lead is completed? I have looked at the Wait Activity Condition but can't seem to get anything to work. Any suggestions appreciated. Thanks, Wendi ...

Work around Conditional Formatting
Dear all, There are 2 worksheets (Sheet1, Sheet2) containing similar data in my workbook. I want to underline the text in a cell in Sheet2 if the text in that cell is the same as the cell in the same place in Sheet1. (For example, if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I write a user-defined function to check whether the contents in two cells are equal? Thanks in advance. Best Regards, Andy Chips has lots of stuff on duplicates, see www.cpearson.com -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Andy Chan" <chankhan...

how to configure multiple email id's in multiple folders
Try posting your Q in the body, and explain what you are trying to achive, together with your version of OL and if its an exchange setup "Ram" <Ram@discussions.microsoft.com> wrote in message news:D0E40903-7D84-49F2-9F91-F97EAB741806@microsoft.com... > ...

insert multiple page Word document into Excel
How do I insert a multiple page Word document into Excel so that I can view the pages by scrolling down the spreadsheet without opening or editing the Word document? Hi this is IMHO not the way Excel will work (and I doubt this is possible) -- Regards Frank Kabel Frankfurt, Germany "S.W." <S.W.@discussions.microsoft.com> schrieb im Newsbeitrag news:D7087D76-A4F3-46BB-90A9-FEB054B24AEB@microsoft.com... > How do I insert a multiple page Word document into Excel so that I can view > the pages by scrolling down the spreadsheet without opening or editing the > Word doc...

Conditional Max value
Hi, i have 2 columns and i'm trying to calculate the conditonal maximum from column one. These are the columns : 47 7 44 7 71 7 58 7 214 4 22 4 54 7 1 7 45 7 21 7 and i try to find a formula that gives the maximum in column one, where column 2 has value 7 in this case this would be 58 Marc Marc, Use the following array formula: =MAX(IF(B1:B10=7,A1:A10,FALSE)) Change the range references to match your data. This is an array formula, so you must press CTRL+SHIFT+ENTER rather than just ENTER when you first enter the for...

Supporting multiple human languages (e.g. English, French, ...) in MFC UI
What's the best approach to supporting multiple languages in an MFC application? I'm expecting to have to have different builds, but I'd like it to be efficient if possible. Thanks. TimHunt, In my experience, for two (or three, if it is one small app) languages, having resources for the different languages in the same exe is ok. More than that, resource DLLs is the way to go. Johan Rosengren Abstrakt Mekanik AB "TimHunt" <timwhunt@yahoo.com> a �crit dans le message de news:On918c3lDHA.2488@TK2MSFTNGP12.phx.gbl... > What's the best approach to supporti...

Multiple emails being sent
Using XP Professional and sometimes recipients get multiple copies - 20 - 30+? What's the fix? ssicfo wrote: > Using XP Professional and sometimes recipients get multiple copies - 20 - > 30+? What's the fix? Get rid of the superfluous e-mail scanner in your anti-virus program. It affords no additional protection beyond the on-access scanner. ...

Multiple xpathexpression with Namespacemanager
Hi Have created an xml document with an xmltextwriter with a namespace, so it can be validated against an xsd schema. Later in my application, I want to extract values from the xml and am using xpathexpression. Because I have a namespace in the xml, I have to use the xmlNameSpaceManager and comile an xpathquery as below: objXMLXPathNav = objXMLXPathDocument.CreateNavigator() '//Add namespace manager 'Dim objExpr As XPathExpression = objXMLXPathNav.Compile("ns1:root/ns1:element1") 'Dim objNamespaces As XmlNamespac...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

Conditional formatting #87
Can I do a conditional formatting on a range if the value in one cell in the range = X in other words can I format the back ground for line 5 if C5 = X Use conditional formating. Select the whole row Set the condition Formula Is =$C$5="X" Choose the Format you want for the cells in that row. -- Regards Roger Govier "ESREI" <anonymous@discussions.microsoft.com> wrote in message news:339b01c51f10$38d61860$a601280a@phx.gbl... > Can I do a conditional formatting on a range if the value > in one cell in the range = X > > in other words can I format the b...