# Count rows with specific number(2) in Multiple Columns

#### Manisstha

##### New Member
Hi, I'm trying to write the formula that will count the number of rows from the sheet where only selected multiple columns (For example: Column C, F, I, L) if has number 2 to be counted. If incase any of the mentioned columns has 2, the row needs to be counted once.

The countifs and sum I m trying either gives me the row for a single row or counts if any of the rows has 2. Could anyone help me with this please?

#### Attachments

• Sample1.PNG
30 KB · Views: 8

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi & welcome to MrExcel.
How about
Excel Formula:
``=SUM(--(MMULT((C1:N1="S1")*(C2:N15=2),SEQUENCE(COLUMNS(C1:N1),,,0))>0))``

Hi & welcome to MrExcel.
How about
Excel Formula:
``=SUM(--(MMULT((C1:N1="S1")*(C2:N15=2),SEQUENCE(COLUMNS(C1:N1),,,0))>0))``
Hi Fluff! And thanks for the welcome.

I tried the formula above but it's not giving me the correct row numbers, I think its only calculating from one single column. Also, is it not possible to select the specific columns that we would like to get the rows for, if any of those columns have 2 on them. (For example, column F and L).

Thanks for the help!

You asked for a formula that will count the number of rows, which is exactly what the formula does.
You will need to specify which columns to look at somehow. If not by the column headers then how?

You asked for a formula that will count the number of rows, which is exactly what the formula does.
You will need to specify which columns to look at somehow. If not by the column headers then how?
Yeah I actually have to check 2 things:
1. The first one is the one mentioned above, count the number of rows if either of the multiple columns have the value =2. The formula you posted does work but it is only calculating the 1st column and giving 5 as sum whereas the total should have been 10. ( I want to accomplish this first as this is my main task to finish.)
2. I have also to calculate the same thing, the number of rows but for columns F and L. (Optional task)

Thanks!

Are you using this in xl365? as it returns 10 for me
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
1S1S2S3S1S2S3S1S2S3S1S2S3
22210
322
42
52
622
722
8
92
10
1122
1222
132
14
152
16
17
Main
Cell Formulas
RangeFormula
O2O2=SUM(--(MMULT((C1:N1="S1")*(C2:N15=2),SEQUENCE(COLUMNS(C1:N1),,,0))>0))

Are you using this in xl365? as it returns 10 for me
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
1S1S2S3S1S2S3S1S2S3S1S2S3
22210
322
42
52
622
722
8
92
10
1122
1222
132
14
152
16
17
Main
Cell Formulas
RangeFormula
O2O2=SUM(--(MMULT((C1:N1="S1")*(C2:N15=2),SEQUENCE(COLUMNS(C1:N1),,,0))>0))
Thank you Fluff! All good now, it was my mistake put the column number wrong.

You're welcome & thanks for the feedback.

Replies
10
Views
226
Replies
1
Views
51
Replies
12
Views
152
Replies
8
Views
128
Replies
4
Views
81

Threads
1,203,465
Messages
6,055,574
Members
444,799
Latest member
CraigCrowhurst

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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