Count cells with numeric value and matching header

[VivoSomnio]

So I want a count for each row of the cells in that row that have a numeric value (not a sum, a count) and match a specific column header. I manually filled out the first one to show what I mean: across row 2 there are 3 cells with a numeric value that match column header of "A", there is 1 cell with a numeric value that matches the column header of "B", and there are 2 cells with a numeric value that matches the column header of "C".

How do I do this as a formula? If you show me how to do it for column header "A" I can replicate it to the additional columns for B and C, I just can't figure out how to do it though. I know how to do it for a sum, =SUMIF(A1:J1,"A",A2:J2) but not for a count. And yes, I realize the picture I did has everything as a 1 but the numeric value isn't always a 1 so I can't use the sumif as a substitute for count.

 

[awoohaw]

This uses the "SUM" function but it gives you a count.

Mr excel questions 59.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		A	B	C	A	B	C	A	B	C		CountA	CountB	CountC
2	Mon	2		8	1	1		1		1		3	1	2
3	Tue	1	1	1		1	1		1			1	3	2
4	Wed		1		1	1		1		1		2	2	1
5	Thur					1	1					0	1	1
6	Fri	1	1						1	1		1	2	1
7	Sat				1	1	1					1	1	1
8	Sun		1	1			1	1				1	1	2
VivoSomnio

Cell Formulas
Range	Formula
L2:N8	L2	=SUM((--(ISNUMBER($B$2:$J$8)))*($A$2:$A$8=$A2)*($B$1:$J$1=RIGHT(B$1,1)))

 

[VivoSomnio]

This uses the "SUM" function but it gives you a count.

Mr excel questions 59.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		A	B	C	A	B	C	A	B	C		CountA	CountB	CountC
2	Mon	2		8	1	1		1		1		3	1	2
3	Tue	1	1	1		1	1		1			1	3	2
4	Wed		1		1	1		1		1		2	2	1
5	Thur					1	1					0	1	1
6	Fri	1	1						1	1		1	2	1
7	Sat				1	1	1					1	1	1
8	Sun		1	1			1	1				1	1	2
VivoSomnio

Cell Formulas
Range	Formula
L2:N8	L2	=SUM((--(ISNUMBER($B$2:$J$8)))*($A$2:$A$8=$A2)*($B$1:$J$1=RIGHT(B$1,1)))

Would it be possible for you to explain this to me so I can adapt it? I thought it would be some sort of count based on the header so I mistakenly didn't include additional details I thought were extraneous. My actual column A is names and goes down hundreds of rows and will grow infinitely, and then my columns extend out also growing infinitely and while we have A, B, C there are additional D, E, F, H that repeat infinitely (though those columns have text not numerical values and I am only interested in the counts of A, B, and C).

I imagined it would be a simple "for this row find every column headed X and then see if the corresponding cell in this row has a numeric value and +1 my count if it does, else ignore it".

 

[awoohaw]

Sure, but some good examples of what your data is is really necessary. I'm not going to guess.

Mr. Excel has an add in, xl2bb that allows you to do that. Link to it is below. If you can't do that, please post a table. No images/pictures/screenshot please.

 

[VivoSomnio]

Sure, but some good examples of what your data is is really necessary. I'm not going to guess again.

I can't share the data due to it being PII and regulations, my apologies. I do believe my original ask explained what I need (a formula to count the number of cells in a row that have a numeric value in the cell if the column header for that cell matches a specific header text).

Hopefully my subsequent response further fleshes out what the data could look like: infinite number of rows, infinite number of columns. Any number of the columns may have an irrelevant header to be ignored, and any number of the columns may have the specific header value I wish to then check the cell for and count if the cell has a number. So think of the original excel body but with hundreds of additional columns randomly inserted (there is no pattern to where the column headers repeat), and hundreds of additional rows with any random name or number.

 

[awoohaw]

Use fake names. and Data. It is not a hard concept. And Mr. Excel forum encourages using simulated data anyway.
You only need 20 rows or so.

Help the forum help you.

 

[kevin9999]

Another option you may be able to 'adapt'

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		A	B	C	A	B	C	A	B	C		CountA	CountB	CountC
2	Mon	2		8	1	1		1		1		3	1	2
3	Tue	1	1	1		1	1		1			1	3	2
4	Wed		1		1	1		1		1		2	2	1
5	Thur					1	1					0	1	1
6	Fri	1	1						1	1		1	2	1
7	Sat				1	1	1					1	1	1
8	Sun		1	1			1	1				1	1	2
Sheet1

Cell Formulas
Range	Formula
L2:L8	L2	=SUMPRODUCT((ISNUMBER($B2:$J2)*($B$1:$J$1="A")))
M2:M8	M2	=SUMPRODUCT((ISNUMBER($B2:$J2)*($B$1:$J$1="B")))
N2:N8	N2	=SUMPRODUCT((ISNUMBER($B2:$J2)*($B$1:$J$1="C")))

 

[awoohaw]

Another option you may be able to 'adapt'

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		A	B	C	A	B	C	A	B	C		CountA	CountB	CountC
2	Mon	2		8	1	1		1		1		3	1	2
3	Tue	1	1	1		1	1		1			1	3	2
4	Wed		1		1	1		1		1		2	2	1
5	Thur					1	1					0	1	1
6	Fri	1	1						1	1		1	2	1
7	Sat				1	1	1					1	1	1
8	Sun		1	1			1	1				1	1	2
Sheet1

Cell Formulas
Range	Formula
L2:L8	L2	=SUMPRODUCT((ISNUMBER($B2:$J2)*($B$1:$J$1="A")))
M2:M8	M2	=SUMPRODUCT((ISNUMBER($B2:$J2)*($B$1:$J$1="B")))
N2:N8	N2	=SUMPRODUCT((ISNUMBER($B2:$J2)*($B$1:$J$1="C")))

But in post #3 he changes his data structure and headers and refuses to help the forum help him. I don't think the OP realizes that people trying to help them are not paid for helping them get paid at their jobs.

 

[kevin9999]

Totally agreed