# Thread: Counting asterisks (Excel 2003)

1. ## Counting asterisks (Excel 2003)

Hi folks....I am attaching a small spreadsheet that I use to track work assignments. The names in black cells, with *** in front of them are special work assignments; I am looking for a formula that will count the number of cells with *** in them......I have tried using a COUNTIF{range, criteria} with the criteria being "***", or "***a" but none of these work...I suspect that it is because * is a wildcard (??).....under the month shown in my sample, I have a cell that contains my attempted formula but you'll see that it is counting every cell that has something in it......on the actual worksheet, the 'empty' cells have formula that are used to populate the cells with data drawn from other sheets......hope this is clear and I thank you for your help......the folks in this forum are very helpful and, I hope to be able to make a contribution as my knowledge of Excel grows....thanks again.

2. ## Re: Counting asterisks (Excel 2003)

You can use this formula:
<code>
=SUMPRODUCT(--(LEFT(A1:U39,3)="***"))
</code>
The double - is used to force the result of the comparison LEFT(A1:U39,3)="***" to a number (1=TRUE or 0=FALSE).

3. ## Re: Counting asterisks (Excel 2003)

In addition to Hans' formula, you could also use COUNTIF(A4:U39,"~*~*~**").

4. ## Re: Counting asterisks (Excel 2003)

Thank you, Hans and Rory......I (think that I ) understand Hans' formula....but I have never seen the ~ used....what does that do, Rory ?.....how does a non-text or non-numeric character 'translate' in a formula ?????

5. ## Re: Counting asterisks (Excel 2003)

The tilda character ~ specifies that the next * or ? is to be treated as a literal character instead of a wildcard. In a criteria string, ? stands for one arbitrary character , but ~? for a question mark. Similarly, * stands for any number of characters, but ~* for an asterisk.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•