Formula to calculate depands on headers

[asyamonique]

Hello All,
I will need help with below tables calculation...
There are huge datas all the way down from column A.
So if its possible I need to place formulas to get proper calculatin base on given table below starts from column J
Many Thanks.



[TABLE="width: 644"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]COLUMN-A-[/TD]
[TD]COLUMN-C-[/TD]
[TD]COLUMN-D-[/TD]
[TD][/TD]
[TD]COLUMN-J-[/TD]
[TD]COLUMN-K-[/TD]
[TD]COLUMN-L-[/TD]
[TD]COLUMN-M-[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]500[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]MONTHS[/TD]
[TD]APPLE[/TD]
[TD]ORANGE[/TD]
[TD]BANANA[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]250[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD]January[/TD]
[TD]500[/TD]
[TD]650[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]400[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD]February[/TD]
[TD]1180[/TD]
[TD]1200[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]600[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]March[/TD]
[TD] [/TD]
[TD]650[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]1200[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD]April[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]580[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]May[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]650[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD]June[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]750[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]July[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]August[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]September[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]October[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]November[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]December[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Assuming Year is Not a factor, and your Column A and J are Text...

Use K2 formula copied down and across, OR, if you Don't want 0 (zero) to show, use O2 formula in K2 copied down and across.

Change/Adjust Cell References/Range as needed:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	January	500		apple						MONTHS	APPLE	ORANGE	BANANA
2	January	250		orange						January	500	650	0		500	650
3	January	400		orange						February	1180	1200	0		1180	1200
4	February	600		apple						March	0	650	750			650	750
5	February	1200		orange						April	0	0	0
6	February	580		apple						May	0	0	0
7	March	650		orange						June	0	0	0
8	March	750		banana						July	0	0	0
9										August	0	0	0
10										September	0	0	0
11										October	0	0	0
12										November	0	0	0
13										December	0	0	0
Sheet462

Cell Formulas
Range	Formula
K2	=SUMIFS($B$1:$B$10,$A$1:$A$10,$J2,$D$1:$D$10,K$1)
O2	=IFERROR(1/(1/SUMIFS($B$1:$B$10,$A$1:$A$10,$J2,$D$1:$D$10,K$1)),"")

 

[asyamonique]

Many Thanks

 

[jtakw]

You're welcome.