Sum amounts from various sheets

[Suresh Kusunuri]

Hi All,

I have 12 sheets work book for Salaries. I wish to calculate total salary of each person during the year.

I used the following formula, but its showing "#REF".
=SUMPRODUCT(SUMIF(INDIRECT(""&Months&EMP),$C5,(INDIRECT("'"&Months&$O$5:$O$72))))
Months = List of Sheets
EMP = List of Employees
Range in RED = Salary Column (in Each Sheet)
$C5 = Employee's Name
Can any one help me in this calculation or suggest other formula

Thanks
Suresh

 

[Dave Patton]

Have you considered using just one sheet?
You will probably receive more replies if you post sample data with the forum's tool named XL2BB.

Edit the following for your data

try a formula like =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A200"),A3,INDIRECT("'"&Sheets&"'!"&"B2:B200")))
or =SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!"&"A5:A72"),C5,INDIRECT("'"&Months&"'!"&"O5:O72")))

 

[Suresh Kusunuri]

Have you considered using just one sheet?
You will probably receive more replies if you post sample data with the forum's tool named XL2BB.

Edit the following for your data

try a formula like =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A200"),A3,INDIRECT("'"&Sheets&"'!"&"B2:B200")))
or =SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!"&"A5:A72"),C5,INDIRECT("'"&Months&"'!"&"O5:O72")))

Thank you Mr. Dave for your support. The said formulas are also showing same results. May be OS or Excel Version is wrong. I am using Windows 11 and Office 16. I am enclosing my work book for your reference and guidance.

Thanks & Regards

 

[Dave Patton]

You did not answer questions or post information that were can use to test a formula!
Please update your profile to show the version of Excel that you are using.
Try
=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!"&"C5:C75"),C5,INDIRECT("'"&Months&"'!"&"D5:D75")))

Ensure that the ranges or references for the names, amounts, and criteria are correct.

 

[Dave Patton]

You did not answer questions or post information that were can use to test a formula!
Please update your profile to show the version of Excel that you are using.
N.B. The monthly sheets must be actual Excel sheets not a png file.
Try naming your sheets similar to 2023_Jan

With Excel 365, try
Try
=SUM(SUMIF(INDIRECT("'"&Months&"'!"&"C5:C75"),C5,INDIRECT("'"&Months&"'!"&"D5:D75"))) or
or
=SUM(SUMIF(INDIRECT(Months&"!C5:C75"),C5,INDIRECT(Months&"!D5:D75")))

Ensure that the ranges or references for the names, amounts, and criteria are correct.

 

[Suresh Kusunuri]

Hi, Mr. Dave

Your formula is working but result is wrong. Plz see the following:

Salaries.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	S.No.	Name of the Employee	Earnings				Deductions						Additions	Net Payable
3			Gross			Total	1	2	3	4	Advance	Total	Welfare
4			Formula	with Cell Ref (Required Result)	Formula works like this
5	1	ALEXANDER	46,666.67	6,133.33	46,666.67		=SUMPRODUCT(SUMIF(INDIRECT("'"&Month_List&"'!"&"EMP"),$C5,INDIRECT("'"&Month_List&"'!"&"$d$5:$d$22")))
6	2	ANDREW	44,783.00	77,133.33	44,783.00
7	3	BUTTLER	42,500.00	78,166.67	42,500.00
8	4	CHERRY	43,066.67	43,616.33	43,066.67							-		-
9	5	DAVID	63,400.33	39,500.00	63,400.33							-		-
10	6	EDEN CLARA	75,166.67	45,200.33	75,166.67							-		-
11	7	FIONA	65,000.00	41,500.00	65,000.00							-		-
12	8	JOHN	63,000.00	35,833.33	63,000.00							-		-
13	9	JOSEPH	55,500.00	15,000.00	55,500.00							-		-
14	10	LILY	71,666.67	36,250.00	71,666.67							-		-
15	11	MASON	37,000.00	38,150.00	37,000.00							-		-
16	12	MATHEW	34,650.00	33,500.00	34,650.00							-		-
17	13	RAYAN	6,133.33	25,000.00	6,133.33							-		-
18	14	REAGAN	13,000.00	6,133.33	13,000.00							-		-
19	15	SMITH	41,666.67	63,750.00	41,666.67							-		-
20	16	STELLA	25,000.00	41,666.67	25,000.00							-		-
21	17	XAVIER	-	13,000.00	-							-		-
22	18	ZION	-	88,666.67	-							-		-
23		Total	7,28,200	7,28,200		-	-	-	-	-	-	-	-	-
24
Summary

Cell Formulas
Range	Formula
K3:L3	K3	='2022_Apr'!J4
D5:D22	D5	=SUMPRODUCT(SUMIF(INDIRECT("'"&Month_List&"'!"&"EMP"),$C5,INDIRECT("'"&Month_List&"'!"&"$d$5:$d$22")))
E5	E5	='2022_Apr'!$D$8
F5:F22	F5	='2022_Apr'!D5+'2022_May'!D5+'2022_Jun'!D5
E6	E6	='2022_Apr'!$D$9+'2022_May'!$D$8+'2022_Jun'!$D$7
E7	E7	='2022_Apr'!$D$12+'2022_May'!$D$11+'2022_Jun'!$D$10
E8	E8	='2022_Apr'!$D$6+'2022_May'!$D$6+'2022_Jun'!$D$5
E9	E9	='2022_Apr'!$D$7+'2022_May'!$D$7+'2022_Jun'!$D$6
E10	E10	='2022_Apr'!D10+'2022_May'!D9+'2022_Jun'!$D$8
E11	E11	='2022_Apr'!$D$11+'2022_May'!$D$10+'2022_Jun'!$D$9
E12	E12	='2022_Apr'!$D$5+'2022_May'!$D$5
E13	E13	='2022_Jun'!$D$16
E14	E14	='2022_May'!$D$14+'2022_Jun'!$D$13
E15	E15	='2022_May'!$D$16+'2022_Jun'!$D$15
E16	E16	='2022_May'!$D$15+'2022_Jun'!$D$14
E17	E17	='2022_Jun'!$D$20
E18	E18	='2022_Jun'!$D$17
E19	E19	='2022_Apr'!$D$13+'2022_May'!$D$12+'2022_Jun'!$D$11
E20	E20	='2022_Jun'!$D$19
E21	E21	='2022_Jun'!$D$18
E22	E22	='2022_Apr'!$D$14+'2022_May'!$D$13+'2022_Jun'!$D$12
M8:M22	M8	=SUM(H8:L8)
O8:O22	O8	=G8-M8+N8
G23:O23,D23:E23	D23	=SUM(D5:D22)

Named Ranges
Name	Refers To	Cells
Month_List	=Summary!$AE$5:$AE$7	D5:D22

It is adding row wise amounts from each sheet, not considering the name (Cell reference "C5" like that, in sumif function).

Kindly do the needful to get the correct result.

Thanks & Regards,

Suresh

 

[Dave Patton]

I do not see the formula that I suggested.
You did not post an example of the data sheets.

try
=SUMPRODUCT(SUMIF(INDIRECT("'"&Month_List&"'!"&"C5:C75"),C5,INDIRECT("'"&Month_List&"'!"&"D5:D75")))

Ensure that the ranges and references for the names, amounts, and criteria are correct.

Test with reference to known information and edit the formula if necessary.

Make a post with a concise version of your data and indicate the result that you expect.

 

[Dave Patton]

In your monthly sheets, what column or range has the employee name?
If the names are in Column B and the required numbers are in Column D, edit the suggested formula to

=SUMPRODUCT(SUMIF(INDIRECT("'"&Month_List&"'!"&"B5:B75"),C5,INDIRECT("'"&Month_List&"'!"&"D5:D75")))

Please edit the formula for the relevant ranges and test.

 

[Dave Patton]

3D Sumif 365.xlsm
	A	B	C	D													Q
1
2		S.No.	Name of the Employee	Earnings													Month_List
3				Gross													2022_Apr
4				Formula													2022_May
5		1	ALEXANDER	100
6		2	ANDREW	300
7		3	BUTTLER	400
8		4	CHERRY	500
9		5	DAVID	600
Summary

Cell Formulas
Range	Formula
D5:D9	D5	=SUMPRODUCT(SUMIF(INDIRECT(Month_List&"!C5:C100"),C5,INDIRECT(Month_List&"!D5:D100")))

Named Ranges
Name	Refers To	Cells
Month_List	=Summary!$Q$3:$Q$4	D5:D9

 

[Suresh Kusunuri]

3D Sumif 365.xlsm
	A	B	C	D													Q
1
2		S.No.	Name of the Employee	Earnings													Month_List
3				Gross													2022_Apr
4				Formula													2022_May
5		1	ALEXANDER	100
6		2	ANDREW	300
7		3	BUTTLER	400
8		4	CHERRY	500
9		5	DAVID	600
Summary

Cell Formulas
Range	Formula
D5:D9	D5	=SUMPRODUCT(SUMIF(INDIRECT(Month_List&"!C5:C100"),C5,INDIRECT(Month_List&"!D5:D100")))

Named Ranges
Name	Refers To	Cells
Month_List	=Summary!$Q$3:$Q$4	D5:D9

Thank you Mr. Dave, its working perfectly. Only thing is the source data should be same size in each month (No of employees).
To get result, I am inserting blank rows to reach the size of Data.
Thank you once again for your support.

Suresh