I need some help creating a formula

[Philea_92]

I am very new to excel and am wondering if anybody would have the time
to look at a formula I need to create.

it is a formula in "totals" sheet

I need it to count all instances for Distribution Centre, Group
desktop, Group laptop etc and wether each instance the person was
available for interview.

I have been using a multple count if statement which works, but the
statemenr I have created does not allow copying across cell ranges.

The formula look like this

=COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "STORE -
TEAMPOS")-COUNTIF('calls closesdon 3 - 4 Dec'!M9:M10, "0")

but if I stetch the cell range M9:M10 to say M9:M15 it messes up the
results because the instaces (either 1 or 0) are instances of another
group.


I would prefer a statement that could be copied across F3 --- F13

if anybody can point me in the right direction I would be greatful.

Attachment filename: customer survey.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=383525

 

[Max]

Assuming you have the set-up:

In sheet named: Totals
----------------------------

(a) In A4:A13, the names of the sheets viz:

calls closed 3, 4 Dec
calls closed 4, 5 Dec
calls closed 6, 7 Dec
calls closed 8 Dec
calls closed 9 Dec
calls closed 10 Dec
calls closed 11 Dec
calls closed 12 Dec
calls closed 13, 14 Dec
calls closed 15 Dec

Notes
--------
1. The sheet names listed in A4:A13
must *match exactly* with the names on the tabs.
You must check carefully and correct this first.
(There were some inconsistencies discovered)

2. All sheets listed are assumed identical in structure
(e.g.: data begins in row4 down, cols are identical, etc)


(b) Across F2:L2 (say), you have listed the “icl.user.type”, e.g.:

In F2: DISTRIBUTION CENTRE
In G2: GROUP DESKTOP
In H2: HIGH STREET DESKTOP
In I2: HIGH STREET LAPTOP
In J2: STORE - FLEXOS
In K2: STORE - NEWPOS
In L2: STORE - TEAMPOS
etc
etc

(As before, the listing in F2:L2 must *match exactly* with the
ones in the “icl.user.type” columns)


c) Put in F4

:
=SUMPRODUCT((INDIRECT("'"&$A4&"'!G4:G100")=F$2)*(INDIRECT("'"&$A4&"'!M4:M100
")=1))

[ I’m reading that you want to count the # of 1’s
in col M for each “icl.user.type” in each sheet,
i.e. that 1's in col M mean the person was available for interview.

If necessary, just amend the last row in the 2 data ranges to suit,
i.e. G4:G100 and M4:M100, if the data ranges will exceed row100.

The 2 ranges in the formula must be identical, ie G4:G100 and M4:M100 ]


Copy F4 down F4:F13

With F4:F13 selected, copy across to L13 (say, assuming L13 is the last col)

(You can also copy F4 across to L4 first, then down to L13)

F4:F13 will return the results for Distribution Centre
G4:G13 will return the results for Group Desktop
And so on..

( If you would like me to send you the completed sample file for reference,
please post your email address in reply to this thread)

 

[Philea_92]

my email address is (e-mail address removed)

I would very much appreciate it if you would send me the file thanks
again.

 

[Max]

File sent, Philea_92!

cheers
Max