Excel formula Count how many rows have a Selections or words

[AdyStewart]

Hello

i need a formula that will count the number of rows in an range B3:G9 that has any of letters in column H in
Example: The formula should return the Value 3 because 3 rows has one of the letters in that row

i did find this but i cant get it to work for me {=SUM(--(MMULT(--(B3:G9="H"),TRANSPOSE(COLUMN(B3:G9)))>0))}

Date	30/06/2019	01/07/2019	02/07/2019	03/07/2019	04/07/2019	05/07/2019	06/07/2019			Letters to count
Name	Sun	Mon	TUE	WED	THU	FRI	SAT
Bill		H	H	UH						UH
Bob		CO	CO							T
Steve										H
Jill		T	T	T
June
Becky
Scott		HA	HP

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

First, I had a bit of trouble matching the ranges in your text with the sample sheet. I copied the sheet and used the resulting range references like that.

Next, the formula you found works to find the number of rows that a single value can be found in. So if you copy that formula down the column next to UH, T, H, then you'll get the number of rows for each code. For example:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
Date	30/06/2019	Letters to count	# of rows
Name	Sun	Mon	TUE	WED	THU	FRI	SAT
Bill	H	H	UH	UH
Bob	CO	CO	T
Steve	H
Jill	T	T	T
June
Becky
Scott	HA	HP

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1/7/2019[/TD]
[TD="align: right"]2/7/2019[/TD]
[TD="align: right"]3/7/2019[/TD]
[TD="align: right"]4/7/2019[/TD]
[TD="align: right"]5/7/2019[/TD]
[TD="align: right"]6/7/2019[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K7[/TH]
[TD="align: left"]=SUM(K3:K5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K3[/TH]
[TD="align: left"]{=SUM(--(MMULT(--($B$3:$H$9=J3),TRANSPOSE(COLUMN($B$3:$H$9)))>0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L7[/TH]
[TD="align: left"]{=SUM(--(MMULT(--ISNUMBER(MATCH($B$3:$H$9,$J$3:$J$5,0)),TRANSPOSE(COLUMN($B$3:$H$9)))>0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Then you can just sum up that column (cell K7) to get the total you want. It is possible to create a single formula to come up with your result, but the results may differ. Look at the L7 formula. It finds the number of rows that any of your codes can be found in. It only counts row 3 once though, since both H and UH are found in it. I've tried coming up with a formula that replicates the value from K7, but it's quite tricky. If I come up with it, I'll let you know.

 

[AdyStewart]

Thank you Eric W that done the job