Countif with weeknum problem

[SteveNL86]

Dear reader,

I have a sheet with data similar to this. I'm using a countif formula to count how many 'Yes' cells I have per day, but I want to count how many unique 'Yes' names I have for each weeknum. I'm having trouble doing a countif with a weeknum criteria.

A - Date (=Date())	B - Weeknum (=Weeknum())	C - Yes/No per Week Total =?	D - Bob	E - Max	F - Rose	G - Anna	H - Nick	I - Ben	J - Marie
15-1-2022	3	3	Yes	Yes	No	No	No	Yes	No
16-1-2022	4	6	No	No	No	No	No	No	No
17-1-2022	4	6	Yes	No	No	No	No	No	No
18-1-2022	4	6	No	No	Yes	No	No	No	No
19-1-2022	4	6	No	No	No	No	No	No	No
20-1-2022	4	6	No	Yes	No	Yes	Yes	No	No
21-1-2022	4	6	No	No	No	No	No	Yes	No
22-1-2022	4	6	No	No	No	No	No	No	No
23-1-2022	5	1	No	No	No	No	No	No	Yes

Any help is much appreciated.

 

[jtakw]

Hi,

This should work:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	A - Date (=Date())	B - Weeknum (=Weeknum())	C - Yes/No per Week Total =?	D - Bob	E - Max	F - Rose	G - Anna	H - Nick	I - Ben	J - Marie
2	15-1-2022	3	3	Yes	Yes	No	No	No	Yes	No
3	16-1-2022	4	6	No	No	No	No	No	No	No
4	17-1-2022	4	6	Yes	No	No	No	No	No	No
5	18-1-2022	4	6	No	No	Yes	No	No	No	No
6	19-1-2022	4	6	No	No	No	No	No	No	No
7	20-1-2022	4	6	No	Yes	No	Yes	Yes	No	No
8	21-1-2022	4	6	No	No	No	No	No	Yes	No
9	22-1-2022	4	6	No	No	No	No	No	No	No
10	23-1-2022	5	1	No	No	No	No	No	No	Yes
Sheet1073

Cell Formulas
Range	Formula
C2:C10	C2	=SUMPRODUCT((B$2:B$10=B2)*(D$2:J$10="Yes"))

 

[SteveNL86]

Thank you jtakw! Working like a charm!

 

[jtakw]

You're welcome, thanks for the feedback.