hardeep.kanwar
Well-known Member
- Joined
- Aug 13, 2008
- Messages
- 693
Hello!
I have Summary Sheet Like
Excel 2007
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 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
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 | |||||||||||
| 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 | |||||||||
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
