Sum of week-Month-Quarter-Year

[Faruk Hosen]

Hello expert,
Greetings from Faruk Hosen Raj!


I have tried different types of sum by searching google and finally looking for the simple and dynamic solution of sum of week-Month-Quarter-Year from sales data of 2 years.

Look forward for a expert advice on that.

Thanks & Regards
Faruk Hosen Raj

 

[Khalid NGO]

Hi Faruk, welcome to the forum

I have used 3 helper columns.

See if this is okay.

Regards,

 

[Jigar B]

you can use SUMPRODUCT formula. I have filled up formula in few columns. you can continue exploring for remaining of your blank cells. please find the attached file.

 

[Faruk Hosen]

Hi Faruk, welcome to the forum

I have used 3 helper columns.

See if this is okay.

Thank you.

you have given me the complete solution. Just a question to clear. would you please explain the logic of 2nd column of helper section.
I don't understand it.

Mr. Jigar B answered a solution of Quarter total. I am sharing.
=SUMPRODUCT((YEAR(B$2:B$100)=2016)*(MONTH(B$2:B$100)={4,5,6})*C$2:C$100)

Let me know your comment.

Regards,

 

[Faruk Hosen]

you can use SUMPRODUCT formula. I have filled up formula in few columns. you can continue exploring for remaining of your blank cells. please find the attached file.

Thank you.
You left me to learn.

=SUMPRODUCT((YEAR(B$2:B$100)=2016)*(MONTH(B$2:B$100)={4,5,6})*C$2:C$100)

What to do if i want to use cell reference instead of {4,5,6}


But i am impressed. It will certainly help me a lot.

Love for Chandoo.

 

[NARAYANK991]

Hi ,

Try this :

=SUMPRODUCT((MONTH($B$2:$B$92) = COLUMN(OFFSET($A$1,,3 *(ROW(A1)-1),,3))) * (YEAR($B$2:$B$92) = F$15) * ($C$2:$C$92))

Enter this in F16 , and copy across and down.

Narayan

 

[Jigar B]

Thank you.
You left me to learn.

=SUMPRODUCT((YEAR(B$2:B$100)=2016)*(MONTH(B$2:B$100)={4,5,6})*C$2:C$100)

What to do if i want to use cell reference instead of {4,5,6}


But i am impressed. It will certainly help me a lot.

Love for Chandoo.

Thank you.
You left me to learn.

=SUMPRODUCT((YEAR(B$2:B$100)=2016)*(MONTH(B$2:B$100)={4,5,6})*C$2:C$100)

What to do if i want to use cell reference instead of {4,5,6}


But i am impressed. It will certainly help me a lot.

Love for Chandoo.

You should have cell with these value to get reference, i.e. some other columns with quarter wise months, needs to be added in sheet (i feel it is unprofessional though). I have added formulas in few more of your blank cells and used more references. File is attached.

 

[Faruk Hosen]

Hi ,

Try this :

=SUMPRODUCT((MONTH($B$2:$B$92) = COLUMN(OFFSET($A$1,,3 *(ROW(A1)-1),,3))) * (YEAR($B$2:$B$92) = F$15) * ($C$2:$C$92))

Enter this in F16 , and copy across and down.

Narayan

Thanks.

Faruk

 

[Faruk Hosen]

You should have cell with these value to get reference, i.e. some other columns with quarter wise months, needs to be added in sheet (i feel it is unprofessional though). I have added formulas in few more of your blank cells and used more references. File is attached.

You are right.
Thanks again.

Faruk Hosen Raj

 

[Khalid NGO]

you have given me the complete solution. Just a question to clear. would you please explain the logic of 2nd column of helper section.
I don't understand it.

Hi Faruk,

Just noticed it is giving wrong week-numbers for some dates.

Please ignore my solution for WEEKS.

Regards,

 

[Faruk Hosen]

Hi Faruk,

Just noticed it is giving wrong week-numbers for some dates.
Please ignore my solution for WEEKS.

Regards,

Noted.
Thanks

 

[Faruk Hosen]

You should have cell with these value to get reference, i.e. some other columns with quarter wise months, needs to be added in sheet (i feel it is unprofessional though). I have added formulas in few more of your blank cells and used more references. File is attached.

Hello,
Look at the following formula

=SUMPRODUCT((YEAR($B$2:$B$100)=2016)*(MONTH($B$2:$B$100)=I3)*$C$2:$C$100)

I want to use a cell I3 which is a date. but not working.
Can you help?

Thanks.

 

[Khalid NGO]

Hi Faruk,

Have you tried adding a MONTH in I3:

=SUMPRODUCT((YEAR($B$2:$B$100)=2016)*(MONTH($B$2:$B$100)=MONTH(I3))*$C$2:$C$100)

Regards,

 

[Faruk Hosen]

Hi Faruk,

Have you tried adding a MONTH in I3:

=SUMPRODUCT((YEAR($B$2:$B$100)=2016)*(MONTH($B$2:$B$100)=MONTH(I3))*$C$2:$C$100)

Regards,

Thanks. It works.