Solved

Sum by Month

Posted on 2016-09-02
13
55 Views
Last Modified: 2016-09-02
Experts, I am looking for a formula to sum by month but the month is text.
I have a spreadsheet attached and think you can see what I mean.

let me know if you need additional info.
EE_sumMonth.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
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41781139
Hi,

pls try

=SUMIF(2:2,MONTH(B7),5:5)

and change the month to date and format it as "MMMM"

Regards
EE_sumMonthV1.xlsx
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41781151
Place the date 8/1/2016 in B7 then in C7 place the below formula
=EOMONTH(B7,0)+1

Open in new window

And copy across and then apply the custom number format to B7:E7 with "mmmm". That will show you the Month names.

Now in B8, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
in B8
=SUM(IFERROR((MONTH($B$1:$FM$1)=MONTH(B7))*$B$5:$FM$5,0))

Open in new window

and then copy across and format as currency.

For details, refer to the attached.
EE_sumMonth.xlsx
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781158
If you have Row2 available with the Month number populated manually, Rgonzo's formula will do the trick.
The formula I suggested didn't use the Row2 as a reference.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41781169
if I understand it correctly...

for column B8, try use formula:
=SUMIF(1:1,DATEVALUE("1 "&OFFSET( B7,0,1) &YEAR(NOW()))-1,5:5  )

Open in new window

but be careful if you handling cases which crossing the year.
EE_sumMonth_b.xlsx
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41781350
wow :)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781356
What happened Professor? :)
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41781362
i opened this question to answer, noticed 3 experts already provided three different solution. :)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781365
That's makes EE special. :)
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41781371
yes :)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781390
If you have a solution different from already posted above, please go ahead and post it.
That would be interesting and I think we learn from each other as well. :)
0
 

Author Closing Comment

by:pdvsa
ID: 41781435
great job again.  You are quite knowledgeable of excel. :0)
0
 

Author Comment

by:pdvsa
ID: 41781439
sorry but I didnt see Rgonzo's response.  I did like the EOMONTH function used by Neeraj.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781494
You're welcome. Glad to help.
And thanks for the feedback and compliment. Much appreciated. :)
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

751 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