# How Do I Count Asterisks

1. ## How Do I Count Asterisks

I need to be able to count the exact number of instances an asterisk is detected in a given row or column. In addition I also need to be able to count blank spaces as well. Help would be extremely helpful, and I will attach an example document of how it would look. Excel Goal.jpg  Register To Reply

2. ## Re: How Do I Count Asterisks

B5: =SUMPRODUCT(LEN(B2:B4)-LEN(SUBSTITUTE(B2:B4,"*","")))
B6: =COUNTBLANK(B2:B4)

repeat for the other ranges ;-)  Register To Reply

3. ## Re: How Do I Count Asterisks

Put these formulae in the cells stated:

F2: =LEN(B2&C2&D2&E2)-LEN(SUBSTITUTE(B2&C2&D2&E2,"*",""))
G2: =LEN(B2&C2&D2&E2)-LEN(SUBSTITUTE(B2&C2&D2&E2," ",""))

and copy down rows 3 and 4.

Put these formulae in the cells stated:

B5: =LEN(B2&B3&B4)-LEN(SUBSTITUTE(B2&B3&B4,"*",""))
B6: =LEN(B2&B3&B4)-LEN(SUBSTITUTE(B2&B3&B4," ",""))

then copy across into columns C to E.

Hope this helps.

Pete  Register To Reply

4. ## Re: How Do I Count Asterisks

JosephP formula with corrected ranges

=SUMPRODUCT(LEN(B2:E2)-LEN(SUBSTITUTE(B2:E2,"*","")))  Register To Reply

5. ## Re: How Do I Count Asterisks

my formula was in b5 not f2 :-)  Register To Reply

6. ## Re: How Do I Count Asterisks Originally Posted by JosephP my formula was in b5 not f2 :-)
Oh, it was for the columns. I looked at the attached file and there is also count for the rows. My apologies   Register To Reply

7. ## Re: How Do I Count Asterisks

no apology necessary! :-)  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 