Excel formula Count how many rows have a Selections or words

AdyStewart

New Member
Joined
Jun 29, 2019
Messages
6
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))}


Date30/06/201901/07/201902/07/201903/07/201904/07/201905/07/201906/07/2019Letters to count
NameSunMonTUEWEDTHUFRISAT
BillHHUHUH
BobCOCOT
SteveH
JillTTT
June
Becky
ScottHAHP

<tbody>
</tbody>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
ABCDEFGHIJKL
1Date30/06/20191/7/20192/7/20193/7/20194/7/20195/7/20196/7/2019Letters to count# of rows
2NameSunMonTUEWEDTHUFRISAT
3BillHHUHUH1
4BobCOCOT1
5SteveH1
6JillTTT
7June32
8Becky
9ScottHAHP

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
K7=SUM(K3:K5)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
K3{=SUM(--(MMULT(--($B$3:$H$9=J3),TRANSPOSE(COLUMN($B$3:$H$9)))>0))}
L7{=SUM(--(MMULT(--ISNUMBER(MATCH($B$3:$H$9,$J$3:$J$5,0)),TRANSPOSE(COLUMN($B$3:$H$9)))>0))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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