Count and Sum from Multiple Sheets

[hardeep.kanwar]

Hello!

I have Summary Sheet Like

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	S.No.	Campus	No. of Registrations	Total Fee Due	Reg Amount Paid	Balance Fee	Cumulative Amount Paid Till Date	Bank Loan	Drop Out
2
3	1	AHMEDABAD	17	3,545,180	20,000	-	458,540	4	-
4	2	BANGALORE	55	12,442,100	50,000	110,300	1,980,600	24	1
5	Grand Total		72	15,987,280	70,000	110,300	2,439,140	28	1
Summary

Excel 2007

Cell Formulas
Range	Formula
D3	=COUNTIF(Table1[Campus Name],C3)
D4	=COUNTIF(Table1[Campus Name],C4)
D5	=SUM(D3:D4)
E3	=SUMIF(Table1[Campus Name],C3,Table1[Total Fee Due For 1st Yr (including Security & Service Tax)])
E4	=SUMIF(Table1[Campus Name],C4,Table1[Total Fee Due For 1st Yr (including Security & Service Tax)])
E5	=SUM(E3:E4)
F3	=SUMIF(Table1[Campus Name],C3,Table1[Reg Amount Paid])
F4	=SUMIF(Table1[Campus Name],C4,Table1[Reg Amount Paid])
F5	=SUM(F3:F4)
G3	=SUMIF(Table1[Campus Name],C3,Table1[Bal Fee Amount Paid])
G4	=SUMIF(Table1[Campus Name],C4,Table1[Bal Fee Amount Paid])
G5	=SUM(G3:G4)
H3	=SUMIF(Table1[Campus Name],C3,Table1[Cummulative Amount Paid Till Date])
H4	=SUMIF(Table1[Campus Name],C4,Table1[Cummulative Amount Paid Till Date])
H5	=SUM(H3:H4)
I3	=COUNTIFS(Table1[Campus Name],C3,Table1[Remarks],"*Bank Loan")
I4	=COUNTIFS(Table1[Campus Name],C4,Table1[Remarks],"*Bank Loan")
I5	=SUM(I3:I4)
J3	=COUNTIFS(Table1[Campus Name],C3,Table1[Remarks],"*Drop oUt")
J4	=COUNTIFS(Table1[Campus Name],C4,Table1[Remarks],"*Drop oUt")
J5	=SUM(J3:J4)

I have total 62 Campuses Data In Same Workbook, I.e. 62 Sheets In One Workbook

In 62 Sheets have Same Column With Same Headers But not Same Rows

I have Convert my Data to Table ( In All 62 Sheets)

Excel Workbook
	A	B	C	D	E	F	G
1	Campus Name	Total Fee Due For 1st Yr (including Security & Service Tax)	Cummulative Amount Paid Till Date	Reg Amount Paid	Bal Fee Amount Paid	Remaining Amt.	Remarks
AllIndiaFeeCollectionsDetai

Excel 2007


Now, I want to Modify my Formula to Count and Sum from All 62 Sheets

Means whenever i Delete or Add the Data in any Sheet it Update the COUNT AND SUM in My Summary Sheet

Sheets Start from

Sheet1= Summary
Sheet2=Ahmedabad
Sheet62 = VIZAG

Hope You Understand my Query or Problem

Regards

Hardeep

 

[hardeep.kanwar]

See

http://www.mrexcel.com/forum/showthread.php?t=119020<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

Thanks Sandeep

You don't Know how really you Help Me for Sending the Link

Much Appreciate

GOD BLESS U