Solved

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

Posted on 2016-08-14
50 Views
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
Question by:pdvsa
• 3
• 2

LVL 28

Expert Comment

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)
``````
0

Author Comment

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

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)
``````
0

Author Closing Comment

ID: 41756011
perfect.  Greatful for your help.
0

LVL 28

Expert Comment

ID: 41756021
You're welcome. Glad I could help.
0

## Join & Write a Comment Already a member? Login.

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!