One set-up to try ...
Assuming the table below is in Sheet1
cols A to D, data from row2 down
type product res_num emp_num
A IA 3 50
B IA 2 60
B R5 1 60
B R5 1 60
B R5 2 60
B R5 2 80
B R5 2 80
C IA 3 40
Using 4 empty cols to the right, say cols F to I
Put in F2:
=IF(AND(OR($A2=Sheet2!$A$1,$A2=Sheet2!$B$1),$B2=Sh eet2!$C$1),C2,"")
Copy F2 across to G2
Put in H2:
=IF(F2="","",IF(COUNTIF(F$2:F2,F2)1,"",ROW()))
Put in I2:
=IF(G2="","",SUMPRODUCT(($F$2:F2=F2)*($G$2:G2=G2)) )
Select F2:I2, copy down to say, I100
to cover the expected data range in the table
In Sheet2
------------
A1:C1 will be earmarked for inputs
Input the "type" into A1:B1, e.g.: B, C (i.e. type = B or C)
Input the "product" into C1, e.g.: R5
Put in D1:
=IF(ISERROR(SMALL(Sheet1!H:H,ROWS($A$1:A1))),"",IN DEX(Sheet1!C:C,MATCH(SMALL
(Sheet1!H:H,ROWS($A$1:A1)),Sheet1!H:H,0)))
Put in E1:
=IF(D1="","",SUMPRODUCT((Sheet1!$F$2:$F$100=D1)*(S heet1!$I$2:$I$100=1)))
Select D1:E1, fill down to E99
(cover the same range as in Sheet1)
Cols D & E will return the desired results for the inputs in A1:C1
For the sample inputs in A1:C1,
you'll get:
1 1
2 2
If A1:C1 contains: A, B, IA
you'll get:
3 1
2 1
and so on ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jim" wrote in message
...
I have a real challenge. I am trying to summarize a large data set.
I need to determine the number of different employees involved in
delivering
service per specific reservation, if the service type and product match
criteria.
Sample data:
type product res_num emp_num
A IA 3 50
B IA 2 60
B R5 1 60
B R5 1 60
B R5 2 60
B R5 2 80
B R5 2 80
C IA 3 40
If type = B or C
And if product = R5
Calculate number of different emp_num utilized per res_num
Sample result:
Res_num Different emp_num
1 1
2 2
Is there a formula(s) that will do this?
Thanks
|