help with a summary table

[cmeredith1973]

Hi - I have this data set. The total of 150.4 on the year to date, or the cost of coffee in 2021 was 150.4

However, the accountants want this expressed so that the sum of the months is 124.7 on the horizontal. and also totals on the vertical like the below. Is there some sort of crafty formula that will help calculate the sum on the vertical and the horizontal?

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

YTD

x

39.2​

27.1​

39.3​

55.0​

46.8​

24.1​

77.8​

28.0​

27.0​

16.2​

20.2​

17.1​

124.7​

y

75.4​

52.2​

37.2​

31.9​

27.1​

14.0​

41.2​

14.8​

14.3​

30.9​

25.4​

32.5​

16.0​

z

46.9​

32.5​

38.2​

43.3​

36.8​

18.9​

42.3​

15.2​

14.7​

34.2​

42.7​

36.0​

9.7​

Company

161.4​

111.8​

114.7​

130.3​

110.7​

57.0​

161.4​

58.0​

55.9​

81.3​

88.3​

85.6​

150.4​

 

[etaf]

If i understand correctly
SUM() would do that
on Row with an X
=SUM( B2:L2)
would give the 124.7, if thats what those values add up to
And then copy down row 2,3 & 4

For the total
=SUM(M2:M4)

I cant change the numbers from text in my spreadsheet copying your data , and dont have time to copy it all across
can you use XL2BB

But i suspect this is not what you mean

Book13
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
2	X	1	6	11	16	21	26	31	36	41	46	51	56	342
3	Y	3	3	3	3	3	3	3	3	3	3	3	3	36
4	Z	5	7	9	11	13	15	17	19	21	23	25	27	192
5	Company	9	16	23	30	37	44	51	58	65	72	79	86	570
Sheet4

Cell Formulas
Range	Formula
N2:N4	N2	=SUM(B2:M2)
B5:N5	B5	=SUM(B2:B4)

 

[Dave Patton]

The question is not clear. Do you want the months to add to the total or do you a correct total?
a guess
Your data is like the following
Select the B15:N18
then click on Aut0Sum icon

T202202a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
14		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
15	x	11.70	8.09	11.73	16.42	13.97	7.19	23.22	8.36	8.06	4.84	6.03	5.09
16	y	3.04	2.10	1.50	1.29	1.09	0.56	1.66	0.60	0.58	1.25	1.02	1.31
17	z	1.13	0.78	0.92	1.05	0.89	0.46	1.02	0.37	0.35	0.83	1.03	0.87
18	Company
3a

AutoSum yields

T202202a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
14		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
15	x	11.70	8.09	11.73	16.42	13.97	7.19	23.22	8.36	8.06	4.84	6.03	5.09	124.70
16	y	3.04	2.10	1.50	1.29	1.09	0.56	1.66	0.60	0.58	1.25	1.02	1.31	16.00
17	z	1.13	0.78	0.92	1.05	0.89	0.46	1.02	0.37	0.35	0.83	1.03	0.87	9.70
18	Company	15.87	10.97	14.15	18.76	15.95	8.21	25.90	9.33	8.99	6.92	8.08	7.27	150.40
19
3a

Cell Formulas
Range	Formula
N15:N18	N15	=SUM(B15:M15)
B18:M18	B18	=SUM(B15:B17)