Grouping Totals

F

Funkydan

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
 
F

Funkydan

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('CS2'!
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top