Solved

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

Posted on 2016-08-14
5
74 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
      [X]
      Welcome to Experts Exchange

      Add your voice to the tech community where 5M+ people just like you are talking about what matters.

      • Help others & share knowledge
      • Earn cash & points
      • Learn & ask questions
      • 3
      • 2
      5 Comments
       
      LVL 31

      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 31

      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 31

      Expert Comment

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

      Featured Post

      PeopleSoft Has Never Been Easier

      PeopleSoft Adoption Made Smooth & Simple!

      On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

      Question has a verified solution.

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

      Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
      Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
      Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
      This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

      732 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