• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need an excel formula to sum data basis the month

Hi All,
Please help me in creating an excel formula where i can get the data monthwise. The raw data is mentioned in weeks and i need it monthly. I have uploaded the file for your kind perusal. I have made 2 formulae but its not working.

Ravi Verma
 
Ravi

Firstly, Welcome to the Chandoo.org Forums

Your file isn't attached

To attach a file use the Upload a File button next to Post Reply below
 
In Cell F3 paste the below formula and drag till F25.
Code:
=TEXT(D3,"mmm")

Now, goto cell I3 and paste the below formula there:

Code:
=SUMIFS(E3:E25,F3:F25,"Jul")

Hope this helps...
 
There are many ways to do this. Here's one which doesn't use helper column

=SUMPRODUCT((TEXT($D$3:$D$25,"yyyym")=TEXT(H3,"yyyym"))*($E$3:$E$25))

and another one that you need to enter as an array formula

=SUM(IF((MONTH(D3:D25)=MONTH(H3))*(YEAR(D3:D25)=YEAR(H3)),E3:E25,))
 
Back
Top