Count Values on a Matrix

[Gimmy88]

Hi,

I have this matrix:

A	B	C
5	5	6
1	3	5
4	2	6
1	5	6

and I would like to count the number of "1", "2", ecc for each column:

	A	B	C
1	2​	0​	0​
1+2	2​	1​	0​
4	1​	0​	0​
6	0​	0​	3​

So a kind of countifs where you can set "A" and "1" as parameters.

Any suggestion?

Thanks,
Andrea

 

[Legacy 143009]

What is 1+2?

 

[Gimmy88]

What is 1+2?

It is the count of the cells where I found "1" and "2", but it is just an example

 

[Legacy 143009]

=SUMPRODUCT(ISNUMBER(MATCH(B$1,Source!$B$1:$D$1,0))*ISNUMBER(MATCH($A2,Source!$A$2:$A$5,0)))

 

[Gimmy88]

=SUMPRODUCT(ISNUMBER(MATCH(B$1,Source!$B$1:$D$1,0))*ISNUMBER(MATCH($A2,Source!$A$2:$A$5,0)))

Thanks!

I didn't test the formula yet, but where can I set "A", "B" and so on?

The point is that I have hundreds of columns and I would like to have the column name as a parameter, so if I had to add a column "AB" between "A" and "B" the formula still works.

 

[Legacy 143009]

This part search for the column headers in source table. You can change $B$1 to your destination referance header:

Also the same goes for the row headers:

 

[Gimmy88]

This part search for the column headers in source table. You can change $B$1 to your destination referance header:
View attachment 81245
Also the same goes for the row headers:
View attachment 81246

• MATCH(B$1,Source!$B$1:$D$1,0)

  returns the position of the value "A" in the first row of the source table, in this case would returns "1"; while

• MATCH($A2,Source!$A$2:$A$5,0)

  returns the position of the value "1" in the first column of the source table, in this case would returns "3"
• So at the end I will have

  SUMPRODUCT(1,3)

  which should returns the value "3", right?

So I don't understand how this formula could returns the count of the value "1" in the column "A" of the source table

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J
1	A	B	C					A	B	C
2	5	5	6				1	2	0	0
3	1	3	5				2	0	1	0
4	4	2	6				1+2	2	1	0
5	1	5	6
Sheet1

Cell Formulas
Range	Formula
H2:J3	H2	=SUMPRODUCT(($A$1:$C$1=H$1)*($A$2:$C$5=$G2))
H4:J4	H4	=SUMPRODUCT(($A$1:$C$1=H$1)*(($A$2:$C$5=1)+($A$2:$C$5=2)))

 

[Legacy 143009]

Ahaha sorry Yes you are totally correct. Tested:

=SUMPRODUCT(ISNUMBER(MATCH($A$1:$C$1,D$7,0))*ISNUMBER(MATCH($A$2:$C$5,$A8,0)))

 

[kvsrinivasamurthy]

Row1	A	B	C	D
Row2	5	3	7	4
Row3	2	1	9	2
Row4	3	1	6	5
Row5	1	6	8	7
Row6
Row7				A	B	C	D
Row8	1			1	2	0	0
Row9	1	2		2	2	0	1
Row10	3			1	1	0	0
Row11	5			1	0	0	1

In Row8 under cell A formula
=LET(a,OFFSET(B2,0,{0,1,2,3},4),b,COUNTIF(a,B8:B11),c,COUNTIF(a,C8:C11),b+c)