?
Solved

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

Posted on 2016-08-14
5
Medium Priority
?
89 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 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: Port Scanner

      Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

      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

      : Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
      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!
      This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
      This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

      850 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