Count Values on a Matrix

Gimmy88

New Member
Joined
Dec 19, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have this matrix:

ABC
556
135
426
156

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

ABC
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Excel Formula:
=SUMPRODUCT(ISNUMBER(MATCH(B$1,Source!$B$1:$D$1,0))*ISNUMBER(MATCH($A2,Source!$A$2:$A$5,0)))
 
Upvote 0
Excel Formula:
=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.
 
Upvote 0
This part search for the column headers in source table. You can change $B$1 to your destination referance header:
1671521748038.png

Also the same goes for the row headers:
1671521797023.png
 
Upvote 0
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
:unsure:
  • Excel Formula:
    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
  • Excel Formula:
    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
    Excel Formula:
    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 😄
 
Upvote 0
Book1
ABCDEFGHIJ
1ABCABC
25561200
31352010
44261+2210
5156
Sheet1
Cell Formulas
RangeFormula
H2: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)))
 
Upvote 0
Ahaha sorry 😂 Yes you are totally correct. Tested:
Excel Formula:
=SUMPRODUCT(ISNUMBER(MATCH($A$1:$C$1,D$7,0))*ISNUMBER(MATCH($A$2:$C$5,$A8,0)))

1671529409414.png
 
Upvote 0
Row1ABCD
Row25374
Row32192
Row43165
Row51687
Row6
Row7ABCD
Row811200
Row9122201
Row1031100
Row1151001

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)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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

Which adblocker are you using?

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
Back
Top