[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • 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
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.

 
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
 
pdvsaProject financeAuthor Commented:
great job again.  You are quite knowledgeable of excel. :0)
0
 
pdvsaProject financeAuthor 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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