Formula Help

[aks_npti]

Hi All,

I have an excel file which is set up like below.

My requirement is : If we input in cell B9 for example as April then in the summation of parameter A in cell C11 should have summation values of A parameter from Jan , Feb , March & April. Likewise for the other parameters C & FC.

I tried sumifs , sumproduct & index formula with not much luck.

I am also attaching the spreadsheet.

Regards,
Aks

 

[NARAYANK991]

Hi ,

If you can change your data layout so that the months are the column headers , and the data A , C and FC are the row headers , the formulae will be very simple.

It is the data layout which is making the formulae complex / complicated.

Narayan

 

[Syedali]

What Mr Narayan Said is the good way.
I did some manual work. Pfa

 

[aks_npti]

Thanks a lot Narayan & Syed however I don't want to change the data layout. I understand that changing the layout will solve but that is not an option.

 

[Hui]

C11: =SUMPRODUCT(($B$5:$S$7)*($B$4:$S$4=$B11)*(INT((COLUMN($B$3:$S$3)-1.1)/3)=((MATCH($B$9,$B$3:$S$3,0)-1)/3)))
Copy down

No changes

 

[Somendra Misra]

Hi,

If you have A,C,FC in the same order for every month, than see look at blue cells but if the order is not same like under JAN you have A,C,FC and under FEB you have FC, C , A than you can use Red color cells formula.

But ideally as @NARAYANK991 Sir pointed this type of layout may look visually very good but creates lots of problems.

Regards,

 

[aks_npti]

Thanks a lot Hui & Somendra . You guys are amazing.

I just now need to tweak a formula a bit so that it do summation retrospectively.

Thanks again!!!

Regards,
Aks