# Count Values on a Matrix



## Gimmy88 (Dec 19, 2022)

Hi,

I have this matrix:


*A**B**C*556135426156

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


----------



## Flashbond (Dec 19, 2022)

What is 1+2?


----------



## Gimmy88 (Dec 19, 2022)

Flashbond said:


> What is 1+2?


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


----------



## Flashbond (Dec 19, 2022)

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


----------



## Gimmy88 (Dec 20, 2022)

Flashbond said:


> ```
> =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.


----------



## Flashbond (Dec 20, 2022)

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 (Dec 20, 2022)

Flashbond said:


> 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 (Dec 20, 2022)

Book1ABCDEFGHIJ1ABCABC255612003135201044261+22105156Sheet1Cell FormulasRangeFormulaH2:J3H2=SUMPRODUCT(($A$1:$C$1=H$1)*($A$2:$C$5=$G2))H4:J4H4=SUMPRODUCT(($A$1:$C$1=H$1)*(($A$2:$C$5=1)+($A$2:$C$5=2)))


----------



## Flashbond (Dec 20, 2022)

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 (Dec 20, 2022)

Row1ABCDRow25374Row32192Row43165Row51687Row6Row7ABCDRow811200Row9122201Row1031100Row1151001

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)


----------



## Gimmy88 (Dec 19, 2022)

Hi,

I have this matrix:


*A**B**C*556135426156

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


----------



## Gimmy88 (Dec 20, 2022)

Thank you all!

At the end I found out also this way:


```
=COUNTIFS(INDEX('Source'!1:1048576,0,MATCH(D14,'Source'!3:3,0)),1)
```


----------

