Help - array formula nightmare!!!!

[geronimo_me]

Hi,

Please can anyone help with a series of array formulas, I receive data
from a scheduled incident report, which looks a bit like the following:

Group Sub GP Duration < 1hr Duration 1-2hrs Duration > 2hrs
A 0 0 2
5
A 1 0 3
0
A 1a 5 0
12
A 2 9 0
15
B 0 1 1
11
B 1 0 0
0
B 1a 2 1
0
B 2 0 2
1

The report I receive is normally contains about 2000 groups and approx
25 more duration columns so I need something set up where i just paste
the report into a spreadsheet and it calculates:

All Group totals ie Group A, including all sub groups and all
durations, so for the above data the result would be: Group A = 51
incidents Group B = 19

All sub group totals, so again for the above data the result would be:
GroupA Sub Group 0 = 7
Sub Group 1 = 3
Sub Group 1a = 17 etc

Any help would be greatly appreciated

Thanks

flick.

 

[Pete_UK]

Can you just clarify how you want the data to be presented? Do you want
a table which shows the Groups and a separate table showing the
sub-groups? Something like:

Total
A x
B x
C x

and

Total
A 0 x
A 1 x
A 1a x
A 2 x
B 0 x
B 1 x
B 1a x
B 2 x


If so, how many main groups do you have (and are these really letters),
and what sub-groups do you have? Will it be okay to have these summary
tables in a separate sheet, so that you can paste your data into Sheet1
each time? If you have 25 more duration columns, does this mean your
main data sheet goes out to column AD ?

Pete

 

[Guest]

It sounds as though a Pivot Table would do what you want, and do so very
easily.

Debra Dalgleish has a ton of tips about pivot tables on her site

http://www.contextures.com/tiptech.html

 

[geronimo_me]

Hi,

I have upto 2000 main groups which are all Teams within a department ie
CST, TAGS1, MNG1 etc. Incidents raised for each team lie in 1 of 7 sub
groups which are 0, 1, 1a, 2, 2a, 3 and 4. And then they sit in 1 of 15
depending on the duration to date of the incident. Durations are <1 hr,
1-2 hrs, 3-7hrs, 7-10hrs etc

Tables you have shown would be a great way to display I just cant get
my head around it. No problem with pasting data into one sheet and
displaying table results into another.

Thanks and much appreciated

Flick. x

 

[geronimo_me]

Hi,

I have upto 2000 main groups which are all Teams within a department ie

CST, TAGS1, MNG1 etc. Incidents raised for each team lie in 1 of 7 sub
groups which are 0, 1, 1a, 2, 2a, 3 and 4. And then they sit in 1 of 15

depending on the duration to date of the incident. Durations are <1 hr,

1-2 hrs, 3-7hrs, 7-10hrs etc


Tables you have shown would be a great way to display I just cant get
my head around it. No problem with pasting data into one sheet and
displaying table results into another.


Thanks and much appreciated


Flick. x

 

[Pete_UK]

It would make things a lot easier if you were to insert a new column C,
with a heading like "Total" in C1 and this formula in C2:

=SUM(D2:AE2)

(or however many duration columns you have). Copy this formula down to
row 2000.

Then in your summary sheet you will need to list in column A all the
main groups that you have, i.e. CST, TAGS1, MNG1 etc. This forms the
upper table, and for the lower table copy the main groups down below,
leaving a gap of a few rows, then put the first of your 7 subgroup
codes in column B and copy down. Then highlight the main groups and
copy the list below itself in column A and put the second of your seven
subgroup codes in B and copy this down. Repeat this for the other 5
sub-group codes. You can then sort the tables so you will have
something like:

CST
MNG1
TAGS1

(gap)

CST 0
CST 1
CST 1a
CST 2
CST 2a
CST 3
CST 4
MNG1 0
MNG1 1
MNG1 1a
MNG1 2
MNG1 2a
MNG1 3
MNG1 4

Hope this helps for now - will return later after some lunch.

Pete

 

[Pete_UK]

Let's say the lower table starts at row 20. Then in C20 enter this
formula:

=SUM(IF((A20&B20)=(Sheet1!A$2:A$2000)&(Sheet1!B$2:B$2000),Sheet1!C$2:C$2000))

This is an array formula, so once you have typed it in (or subsequently
edit it) use CTRL-SHIFT-ENTER rather than just ENTER. If you do this
correctly then Excel will wrap curly braces { } around the formula - do
not type these yourself. The formula can then be copied down to the
bottom of the lists in the lower table.

The upper table can make use of SUMIF( ) as there is only one
condition. Enter this formula in B2 (or C2 if you prefer) if your table
data starts in A2:

=SUMIF(Sheet1!A$2:A$2000,A2,Sheet1!C$2:C$2000)

Then you can copy this down the upper table.

Hope this helps.

Pete