COUNTIF/MATCH/INDEX question

[colm]

I know the answer is probably obvious to some of you but I cant see it it. I have a table of employees and week numbers like so


Week Number Employee
1 .................Mike
1................. Mike
1................. Mike
1................. John
1................. John
2................. Mike
2................. John
2 .................John
3 .................John
3 .................John
3 .................John
3 .................Mike
3 .................John
3 .................John
4 .................John
4 .................Mike
4 .................Mike
4 .................John
5 .................Mike
5 .................Mike
5 .................Mike


I add to this most days. I have tables running off this for each of the weeks that look like this


Week 5
John.......2
Mike ......3
Total ......5

When I add a new row onto the master table eg, 5, Mike I want my secondary tables to update automatically. I've tried COUNTIF and MATCH/INDEX but can't seem to get it right.

It can be done on a pivot table but a pivot is not appropriate in this case.

 

[colm]

What ff I use COUnTIF, AND and another COUNTIF? I can never use AND or OR properly. I'm not sure of the syntax.

 

[Aladin Akyurek]

What ff I use COUnTIF, AND and another COUNTIF? I can never use AND or OR properly. I'm not sure of the syntax.

Are you on Excel 2003?

 

[colm]

No. I'm on Excel 2000.

 

[Krishnakumar]

Hi,

Have a look..

Book1
	A	B	C	D	E	F
1	Week Number	Employee		Week Number	5	23
2	1	Mike		Mike	3
3	1	Mike		John	1
4	1	Mike
5	1	John
6	1	John
7	2	Mike
8	2	John
9	2	John
10	3	John
11	3	John
12	3	John
13	3	Mike
14	3	John
15	3	John
16	4	John
17	4	Mike
18	4	Mike
19	4	John
20	5	Mike
21	5	Mike
22	5	Mike
23	5	John
Sheet1

Formula in F1:

=MATCH(100,A:A)

In E2 and copy down,

=SUMPRODUCT(--($A$2:INDEX(A:A,$F$1)=$E$1),--($B$2:INDEX(B:B,$F$1)=D2))

HTH

 

[colm]

I'll give this a go.

Thanks KK.

colm

 

[colm]

Works perfectly.


Brilliant!!

Thanks


Colm