[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2016-08-14
5
Medium Priority
?
85 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 33

      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 33

      Accepted Solution

      by:
      Subodh Tiwari (Neeraj) earned 2000 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 33

      Expert Comment

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

      Featured Post

      Free Tool: Site Down Detector

      Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

      One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

      Question has a verified solution.

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

      Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
      How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
      The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
      This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

      649 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