x
Solved

# Sum by Month

Posted on 2016-09-02
Medium Priority
65 Views
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
Question by:pdvsa
• 6
• 3
• 2
• +2

LVL 55

Expert Comment

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 36

Accepted Solution

Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41781151
Place the date 8/1/2016 in B7 then in C7 place the below formula
``````=EOMONTH(B7,0)+1
``````
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))
``````
and then copy across and format as currency.

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

LVL 36

Expert Comment

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

LVL 56

Expert Comment

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  )
``````
but be careful if you handling cases which crossing the year.
EE_sumMonth_b.xlsx
0

LVL 27

Expert Comment

ID: 41781350
wow :)
0

LVL 36

Expert Comment

ID: 41781356
What happened Professor? :)
0

LVL 27

Expert Comment

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

LVL 36

Expert Comment

ID: 41781365
That's makes EE special. :)
0

LVL 27

Expert Comment

ID: 41781371
yes :)
0

LVL 36

Expert Comment

ID: 41781390
That would be interesting and I think we learn from each other as well. :)
0

Author Closing Comment

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

Author Comment

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

LVL 36

Expert Comment

ID: 41781494
And thanks for the feedback and compliment. Much appreciated. :)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.