Count rows with specific number(2) in Multiple Columns

[Manisstha]

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?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(--(MMULT((C1:N1="S1")*(C2:N15=2),SEQUENCE(COLUMNS(C1:N1),,,0))>0))

 

[Manisstha]

Hi & welcome to MrExcel.
How about

=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!

 

[Fluff]

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?

 

[Manisstha]

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!

 

[Fluff]

Are you using this in xl365? as it returns 10 for me

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			S1	S2	S3	S1	S2	S3	S1	S2	S3	S1	S2	S3
2			2						2						10
3					2							2
4				2
5			2
6			2			2
7			2			2
8
9												2
10
11						2			2
12			2						2
13									2
14
15												2
16
17
Main

Cell Formulas
Range	Formula
O2	O2	=SUM(--(MMULT((C1:N1="S1")*(C2:N15=2),SEQUENCE(COLUMNS(C1:N1),,,0))>0))

 

[Manisstha]

Are you using this in xl365? as it returns 10 for me

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			S1	S2	S3	S1	S2	S3	S1	S2	S3	S1	S2	S3
2			2						2						10
3					2							2
4				2
5			2
6			2			2
7			2			2
8
9												2
10
11						2			2
12			2						2
13									2
14
15												2
16
17
Main

Cell Formulas
Range	Formula
O2	O2	=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.

 

[Fluff]

You're welcome & thanks for the feedback.