Need Formulas from source data(Sheet1) to Sheet2 .

[click2chaitu]

Hi All,

I have data of 3 companies Change percentage data for multiple years according to months in Sheet1 as below


Requirement

Need formula to sum up all the data of all years according to months w.r.t company with filter by year in Sheet2 in below format



Attached the Excel Sheet for reference.
Please help on this. If you need any details please let me know.

Thanks.

 

[GraH - Guido]

Hi,

seems easy to do with a pivot and slicers. No formula required whatsoever.
Just one question/remark. Summing up these percentages per month, what's that going to tell you?

 

[click2chaitu]

Hi Grah,

Thanks for you quick reply. It will be easy for me if you provide formula because i have so many others companies to do. i can't do with pivot all the time.
your question: These are the sales figure, with these i can get to know in which month getting high/low sales.

if you provide me with formula for this , it will be very helpful.

Thanks.

 

[Somendra Misra]

Hi,

Your question statement has some problem, if you want to sum of all the years (let say for the month Jan) than what good is the use of year filter.

Although you can use below file, it uses Microsoft 365 latest formulas.

Regards,

 

[click2chaitu]

Hi,

Your question statement has some problem, if you want to sum of all the years (let say for the month Jan) than what good is the use of year filter.

Regards,

Hi Somendra,

Thanks for your reply. Either anyone will work for me with/without filter.
if you can provide without filter it is fine for me.

Thanks.

 

[Somendra Misra]

Hi Somendra,

Thanks for your reply. Either anyone will work for me with/without filter.
if you can provide without filter it is fine for me.

Thanks.

I had already attached my solution in my last reply. Please refer it.

Regards,

 

[bosco_yip]

Formula solution

1] Changed your table column header in B1:M1 to "Jan", "Feb", "Mar"…......."Dec" as per following screenshot table.

Then,

2] In B2, formula copied across and down :

=SUMIFS(OFFSET(Sheet1!$B$1,0,MATCH($A2,Sheet1!$C$1:$E$1,0),1048576),Sheet1!$B:$B,B$1)

 

[click2chaitu]

Thank You Somendra and Bosco. It is very helpful to me.

 

[Somendra Misra]

Hi,

Another formula solution:

Use below layout


Formula in B2 and copy across and down

=SUMPRODUCT(--(Sheet1!$B$2:$B$355=Sheet2!B$1),INDEX(Sheet1!$C$2:$E$355,,MATCH(Sheet2!$A2,Sheet1!$C$1:$E$1,0)))

Regards,