Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Sum by Month

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
pdvsa
Asked:
pdvsa
  • 6
  • 3
  • 2
  • +2
1 Solution
 
Rgonzo1971Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ryan ChongCommented:
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
 
ProfessorJimJamCommented:
wow :)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What happened Professor? :)
0
 
ProfessorJimJamCommented:
i opened this question to answer, noticed 3 experts already provided three different solution. :)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's makes EE special. :)
0
 
ProfessorJimJamCommented:
yes :)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
pdvsaAuthor Commented:
great job again.  You are quite knowledgeable of excel. :0)
0
 
pdvsaAuthor Commented:
sorry but I didnt see Rgonzo's response.  I did like the EOMONTH function used by Neeraj.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
And thanks for the feedback and compliment. Much appreciated. :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now