Counting character entries while excluding others

Miklopolis

New Member
Joined
Nov 22, 2004
Messages
13
Hi there and thanks for looking. I am attempting to count the number of times any character string appears in a range. In my example I would like to count "STL" and "NYI" but not "—". I know about the COUNTA function and the COUNTIF function. I attempted using a combo. of IF and COUNTA but was unable to get the result I want. Is there a way I can count only the cells where the value is not equal to — ?

Thanks!

MON TUE WED Oct
9-Oct 10-Oct 11-Oct 4th-11th
STL — NYI 2 should go here
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
This formula will count cells not equal to "-"

=COUNTIF(range,"<>-")

but it will include blanks

perhaps

=SUM(COUNTIF(range,{"STL","NYI"}))
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
Try:

=SUMPRODUCT(--(LEN(A2:E2)=3),--(A2:E2<>"-"))
 

Forum statistics

Threads
1,140,912
Messages
5,703,128
Members
421,276
Latest member
davidfrommke

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
Top