Solved

Sum data for a given range of months (if months are number or text)

Posted on 2016-08-14
5
56 Views
Last Modified: 2016-08-15
Experts,

if given:
1/1/2016   1/2/2016  2/1/2016  2/15/2016  3/1/2016
with costs of
10/20/40/50

    Sum the costs for these months:
    if month is text:
    January, February, March

      if months are given in month format:
      1/1/2016; 2/10/2016; 3/25/2016

      What is the formula to sum those costs based on the months?
      I am not sure if I should use SUMIF (as an array) or SUM PRODUCT.

      Please see attached spreadsheet with necessary data and the formulas I have used but note they are not correct formulas.  

      thank you in advance....
      EE-SumIF-Dates.xlsx
      0
      Comment
      Question by:pdvsa
      • 3
      • 2
      5 Comments
       
      LVL 28

      Expert Comment

      by:Subodh Tiwari (Neeraj)
      ID: 41755477
      See if this works for you...

      =SUMPRODUCT(($B$5:$ALD$5<>"")*(TEXT($B$5:$ALD$5,"mmmm")=B$15)*$B$6:$ALD$6)
      

      Open in new window

      0
       

      Author Comment

      by:pdvsa
      ID: 41755526
      nice.  it worked for the months in text.
      Would you have a suggestion for the months in digit format (ie 1/1/2016)

      thank you
      0
       
      LVL 28

      Accepted Solution

      by:
      Subodh Tiwari (Neeraj) earned 500 total points
      ID: 41755530
      If the dates criteria are in row7, try this....

      =SUMPRODUCT(($B$5:$ALD$5<>"")*(MONTH($B$5:$ALD$5)=MONTH(B$7))*$B$6:$ALD$6)
      

      Open in new window

      0
       

      Author Closing Comment

      by:pdvsa
      ID: 41756011
      perfect.  Greatful for your help.
      0
       
      LVL 28

      Expert Comment

      by:Subodh Tiwari (Neeraj)
      ID: 41756021
      You're welcome. Glad I could help.
      0

      Featured Post

      Is Your Active Directory as Secure as You Think?

      More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

      Question has a verified solution.

      If you are experiencing a similar issue, please ask a related question

      INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
      This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
      This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
      This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

      911 members asked questions and received personalized solutions in the past 7 days.

      Join the community of 500,000 technology professionals and ask your questions.

      Join & Ask a Question

      Need Help in Real-Time?

      Connect with top rated Experts

      20 Experts available now in Live!

      Get 1:1 Help Now