Count number of rows where one criteria is a sum of columns

[dpatel20]

Hi,

I have a register (attendance) with columns as follows:

Name | Yr Grp | Date1 | Date2....| Date 10

What I want to do is count the number of rows where Yr Grp=N (a number) AND where the count of Date1 to Date10 =P (present) is greater than 7. i.e. how many people (each row is a person's attendance record) are in Yr Grp=N AND have attended 7 out of 10 dates (does not need to be contiguous).

I have tried various SUMPRODUCT() formulae but to no avail. Any ideas how I can achieve this?

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Yr Grp	D1	D2	D3	D4	D5	D6	D7	D8	D9	D10		1	2
2
3		1	1	2	3	4	5	6	7
4		2
5		3
6		1	1		1		1		1
7		2
8		3
9		1	1	1		1	1		1	1		1
10		2
11		3

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula O1 {=SUM((B3:B11=N1)*(MMULT(--(C3:L11<>""),{1;1;1;1;1;1;1;1;1;1})>=7))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Make sure to use Control+Shift+Enter.


Edit: Without CSE:

=SUMPRODUCT((B3:B11=N1)*(MMULT(--(C3:L11<>""),{1;1;1;1;1;1;1;1;1;1})>=7))

 

[dpatel20]

Thanks Eric - I used the SUMPRODUCT version and it worked a treat. I had a feeling mmult() would come into it somewhere but had no idea how!

 

[Eric W]

Glad it worked out for you!