Count unique values in week

[Remsnow]

Hi

We have two sheets, one with candidate booking information(booked) and one with candidate payment information (paid). Both spreadsheetshave the week number in a text value ‘Week 1’ , ‘Week 2’, ‘Week 3’ etc. What weare needing to do is find out the number of unique workers that were booked andnumber of workers paid in a given week. So on the front page we will be doing asummary with a column of week numbers i.e. Week 1, Week 2 etc and column headers'Booked' – 'Worked' . So what we want to do is ‘if the column in week 1 = thesummary ‘week 1’, count the unique number of workers listed in the ‘bookedsheet’. Equally we also want the same lookup to go and see ‘if the data in‘paid’ spreadsheet equals week 1, go and give us a unique count.


I understand sumifs and countifs I just cannot figure outhow to go and do a unique count if this is even possible?

 

[AhoyNC]

Maybe something like this:
These are array formulas and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
These formulas assume there will be no blanks in the name column.

Excel Workbook
	A	B	C	D	E	F
1	Week	Employee		Count	Booked	Paid
2	Week1	Name1		Week1	3	2
3	Week1	Name2		Week2	2	2
4	Week1	Name1		Week3	3	2
5	Week1	Name1
6	Week1	Name4
7	Week2	Name2
8	Week2	Name1
9	Week2	Name2
10	Week3	Name2
11	Week3	Name2
12	Week3	Name4
13	Week3	Name1
14	Week3	Name4
Booked

Excel Workbook
	A	B
1	Week	Employee
2	Week1	Name1
3	Week1	Name1
4	Week1	Name1
5	Week1	Name4
6	Week2	Name2
7	Week2	Name1
8	Week2	Name2
9	Week3	Name2
10	Week3	Name2
11	Week3	Name4
Paid

 

[Remsnow]

Hi there! Thank you very much for the response. I have managed to get the E2 formula to work I just cannot get the CELL F2 formula to work, it says I have entered too few arguments for the function!

Do you know what the issue might be please?

 

[Remsnow]

Hi, sorry my fault! There was a missing ) !

Thank you so much for your help, this is amazing, thank you, really appreciate!