need an excel formula to sum data basis the month

[Ravi Verma]

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

 

[Hui]

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

 

[Sam Mathai Chacko]

File missing

 

[Ravi Verma]

file attached

 

[Abhijeet R. Joshi]

In Cell F3 paste the below formula and drag till F25.

=TEXT(D3,"mmm")

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

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

Hope this helps...

 

[Sam Mathai Chacko]

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,))

 

[Haseeb A]

Hello Ravi,

Here is another way:

=SUM(SUMIF(D:D,">"&EOMONTH(H3,{-1,0}),E:E)*{1,-1})

 

[Ravi Verma]

Thx Sam n Haseeb for your help .. it really works now