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

#### dpatel20

##### New Member
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?

Last edited:

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Eric W

##### MrExcel MVP
Try:

ABCDEFGHIJKLMNO
1NameYr GrpD1D2D3D4D5D6D7D8D9D1012
2
311234567
42
53
611111
72
83
911111111
102
113

<tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
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))

Last edited:

#### dpatel20

##### New Member
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

##### MrExcel MVP
Glad it worked out for you!

Replies
9
Views
312
Replies
4
Views
232
Replies
1
Views
290
Replies
4
Views
316
Replies
4
Views
240

1,195,924
Messages
6,012,339
Members
441,691
Latest member
starlightmuse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back