Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Totals
Hello Every one,
i was wondering if some one can help me, which some coding or formula, What is, that I've created a cost sheet, for employees to work on, A work sheet represents a section within in a breakdown on costing a projects, (There About 25 Identical cost sheets) the cost sheet and these fields | A | B | C | D | E | 1| QTY Description Unit Cost Total Cost CGC 2| CGC Stands for Cost Group code (The Codes are 1 to 14) What have is a cost group summary sheet as well. Listed the 1 to 14 with there Description The cost sheet will look like something like this below | A | B | 1 | Total Cost 2 | 3 | 01 Equipment 4 | 02 Air Conditioning 5 | 03 Etc What i would like to happen is that in cell B2 is that it adds all the items that have CGC 1 assigned to them in the cost sheets i think it would be some sort of if statement like IF"AllSheets"Column B"=1 (Add Column D) Does this make sense? Can anyone help please Danny |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Totals
On 15 Aug, 11:09, Funkydan wrote:
Hello Every one, i was wondering if some one can help me, which some coding or formula, What is, that I've created a cost sheet, for employees to work on, A work sheet represents a section within in a breakdown on costing a projects, (There About 25 Identical cost sheets) the cost sheet and these fields | A | B | C | D | E | 1| QTY Description Unit Cost Total Cost CGC 2| CGC Stands for Cost Group code (The Codes are 1 to 14) What have is a cost group summary sheet as well. Listed the 1 to 14 with there Description The cost sheet will look like something like this below | A | B | 1 | Total Cost 2 | 3 | 01 Equipment 4 | 02 Air Conditioning 5 | 03 Etc What i would like to happen is that in cell B2 is that it adds all the items that have CGC 1 assigned to them in the cost sheets i think it would be some sort of if statement like IF"AllSheets"Column B"=1 (Add Column D) Does this make sense? Can anyone help please Danny Hello Again, Ive Managed to get what i want to work, by using the following formula in the Cost Group Code Sheet, =SUMIF('CS1'!I10:I33,"=2",'CS1'!F10:F33)+SUMIF('CS 2'! I10:I33,"=2",'CS2'!F10:F33) +SUMIF('CS3'!I10:I40,"=2",'CS3'!F10:F33) +SUMIF('CS4'!I10:I40,"=2",'CS4'!F10:F33) +SUMIF('CS5'! I10:I40,"=2",'CS5'!F10:F33) +SUMIF('CS6'!I10:I40,"=2",'CS6'!F10:F33) +SUMIF('CS7'!I10:I40,"=2",'CS7'!F10:F33) +SUMIF('CS8'! I10:I40,"=2",'CS8'!F10:F33) +SUMIF('CS9'!I10:I40,"=2",'CS9'! F10:F33)+SUMIF('CS10'!I10:I40,"=2",'CS10'!F10:F33) +SUMIF('CS11'! I10:I40,"=1",'CS11'!F10:F33) +SUMIF('CS12'!I10:I40,"=2",'CS12'! F10:F33) +SUMIF('CS13'!I10:I40,"=2",'CS13'!F10:F33) +SUMIF('CS14'! I10:I40,"=2",'CS14'!F10:F33) +SUMIF('CS15'!I10:I40,"=2",'CS15'! F10:F33) +SUMIF('CS16'!I10:I40,"=2",'CS16'!F10:F33) +SUMIF('CS17'! I10:I40,"=2",'CS17'!F10:F33) +SUMIF('CS18'!I10:I40,"=2",'CS18'! F10:F33) +SUMIF('CS19'!I10:I40,"=2",'CS20'!F19:F33) +SUMIF('CS16'! I10:I40,"=2",'CS20'!F10:F33) +SUMIF('CS21'!I10:I40,"=2",'CS21'! F10:F33) +SUMIF('CS22'!I10:I40,"=2",'CS22'!F10:F33) +SUMIF('CS23'! I10:I40,"=2",'CS23'!F10:F33) +SUMIF('CS24'!I10:I40,"=2",'CS24'! F10:F33) But I have a Few Limitations, As follows The Formula is very long winded! Can it be simplified? Secondly I'm only limited to having 24 Cost Sheets as the formula is to long to add any more. Thirdley if a users decides to rename a Sheet Tab, it will cause issue, and the formula text field is not long enough does any one have a work arounds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
Grouping & Group Totals | Excel Worksheet Functions | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
Grouping | Excel Discussion (Misc queries) | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |