Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Counting asterisks (Excel 2003)

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

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •