Excel Formulas

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Hi,

Right firstly I would like to thank Don, Paul and all who
have already helped me, it has been greatly appreciated.
It has now been dumped on me that I need to get results
for all our teams across the dept Mon - Fri across the
weeks.

We have 7 managers working various days, under them they
have telephone operators, the same person can log a
different query with a differnent manager but on the same
day so I need to through all the managers data. In turn
the operators take phone calls and log these with the
manager, I would like to count how many/different types of
queries or calls we are recieving. These are logged
randomly so the formula needs to be able to count/search
for specfied criteria.

The formula I have is:

=SUMPRODUCT(('[Anthony Week 42.xls]Wednesday'!C2:C50="Tina
Shields")*('[Anthony Week 42.xls]Wednesday'!
B2:B50="Outstanding Balance"))

On the summary sheet this formula counts how
many "Outstanding Queries" Tina Shields recieved on
Wednesday on that particular week. The formula works and
does count only if the criteria matches "Outstanding
Queries and Tina Shields" but the problem is that I 7
teams and 5 working days so I need 35 of these formula's
above to search for Tina and the Query but the formula is
way too long and won't hold in the cell.

Is there a neater or better method of doing this, than the
basic route I'm using already???


Many Thanks in advance

Simon
 
Simon said:
Hi,

Right firstly I would like to thank Don, Paul and all who
have already helped me, it has been greatly appreciated.
It has now been dumped on me that I need to get results
for all our teams across the dept Mon - Fri across the
weeks.

We have 7 managers working various days, under them they
have telephone operators, the same person can log a
different query with a differnent manager but on the same
day so I need to through all the managers data. In turn
the operators take phone calls and log these with the
manager, I would like to count how many/different types of
queries or calls we are recieving. These are logged
randomly so the formula needs to be able to count/search
for specfied criteria.

The formula I have is:

=SUMPRODUCT(('[Anthony Week 42.xls]Wednesday'!C2:C50="Tina
Shields")*('[Anthony Week 42.xls]Wednesday'!
B2:B50="Outstanding Balance"))

On the summary sheet this formula counts how
many "Outstanding Queries" Tina Shields recieved on
Wednesday on that particular week. The formula works and
does count only if the criteria matches "Outstanding
Queries and Tina Shields" but the problem is that I 7
teams and 5 working days so I need 35 of these formula's
above to search for Tina and the Query but the formula is
way too long and won't hold in the cell.

Is there a neater or better method of doing this, than the
basic route I'm using already???


Many Thanks in advance

Simon
Click to expand...

If your method of recording could be modified, this would help in analysing
the data. Or, if you are analysing retrospectively, you could combine the
separate day sheets and sort the data into a friendlier format for what you
want.

If you must analyse from data in the form you describe, you could have 35
separate formulas in 35 cells and total the results separately in a 36th
cell.
 
The first step should be to combine the 35 separate data
sheets into a single table. This combined table would look
like this:

Manager | Week | Day | Operator | Call Type
-------------------------------------------------
Anthony 41 Mon Tina Type_A
Anthony 41 Mon Tina Type_A
Phil 41 Tue Bob Type_A
Phil 41 Wed Dan Type_C
Amy 41 Thur Tina Type_A
Anthony 42 Mon Tina Type_A
Anthony 42 Tue Bob Type_A
Anthony 42 Wed Dan Type_C
Phil 42 Mon Jill Type_B
Phil 42 Mon Tina Type_B
Phil 42 Fri Jill Type_B
Phil 42 Fri Jill Type_B
Phil 42 Fri Bob Type_B

Once this table is assembled it is easy to summarise the
data in any way you want by using a pivot table. E.g Call
Type by week, by Manager + Week, by Manager + Day +
Operator, by Operator only etc.

The proces of combining the separate sheets in different
workbooks can easily be automated. Post more info if you
want help with this- e.g. Is the filename/sheet name
always of the form:
[Manager] Week [Week Num]![Day of Week]

Cheers,
Dave.
-----Original Message-----
Hi,

Right firstly I would like to thank Don, Paul and all who
have already helped me, it has been greatly appreciated.
It has now been dumped on me that I need to get results
for all our teams across the dept Mon - Fri across the
weeks.

We have 7 managers working various days, under them they
have telephone operators, the same person can log a
different query with a differnent manager but on the same
day so I need to through all the managers data. In turn
the operators take phone calls and log these with the
manager, I would like to count how many/different types of
queries or calls we are recieving. These are logged
randomly so the formula needs to be able to count/search
for specfied criteria.

The formula I have is:

=SUMPRODUCT(('[Anthony Week 42.xls]Wednesday'! C2:C50="Tina
Shields")*('[Anthony Week 42.xls]Wednesday'!
B2:B50="Outstanding Balance"))

On the summary sheet this formula counts how
many "Outstanding Queries" Tina Shields recieved on
Wednesday on that particular week. The formula works and
does count only if the criteria matches "Outstanding
Queries and Tina Shields" but the problem is that I 7
teams and 5 working days so I need 35 of these formula's
above to search for Tina and the Query but the formula is
way too long and won't hold in the cell.

Is there a neater or better method of doing this, than the
basic route I'm using already???


Many Thanks in advance

Simon
.
Click to expand...
 
Thank you Dave R and Paul once again, I have taken aboard
your suggestions and I'll try them, the pivot table idea
seems a good one but when I try to make it just confuses
the hell out of me??

Paul, I don't think I'll be able to count the 35 ranges
separately and then total them in cell 36, this is because
there is 20 queries and doing this would make one big
sheet (e.g 20*36=720)

The summary needs to look like this:

Refund--Amend--Sale--Proof--Dispute etc
Jane 1 0 16 0 3
Sarah 0 0 0 1 4
Michael 2 72 1 0
John
David
Simon
Terry
Chloe
etc..

So from the summary we can see from all the managers sheets
over the week it seems that Michael is the king
of "amendment queries". Right I'll try to compile a pivot
table and see where I get but if anyone has any
suggestions there more than welcomed...


Many Thanks in advance

Simon (A Highly stressed admin guy)
 
Simon,

Here's a step by step on doing the pivot table. I'll
assume your table looks like the example in my previous
post.

1) Select the table, including column headers
2) Menu: Data/Pivot Table and Pivot Chart Report...
3) Next
4) Next
5) Click Layout button
6) Drag the Operator field button over the Row area
7) Drag the Call Type button over the Column area
8) Drag a second Call Type button over the data area,
which will then show the caption 'Count of Call Type'
9) Click OK on the Layout dialog
10) Select where you want the pivot table- best to go for
a new sheet
11) Click Finish on the wizard dialog

Now you've got the basic pivot table set up you can change
it around very easily. For example, if you want to re-
order the columns simple drag the cell containing the
column heading into the desitred position. If you want to
split the data down by Week Number then drag the Week
Number field button from the floating menu (this should
appear whenever you click in the pivot table) and drop
over the existing Operator field button.

If the data in the source data table changes, right
click in the pivot table and select Refresh Data.

If you add more data to the end of the source data table
then right-click in the pivot table and select Wizard.
Click Back, then update the source range in the box, then
click Finish. The new data will now be included.

If you want to make a chart of this data, click anywhere
within the pivot table then click the chart button on the
floating pivot table menu. You can add/remove fields etc
on the chart and the changes will be reflected in the
table.

Cheers,
Dave
 
Back
Top