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
B5: =SUMPRODUCT(LEN(B2:B4)-LEN(SUBSTITUTE(B2:B4,"*","")))
B6: =COUNTBLANK(B2:B4)
repeat for the other ranges ;-)
Josie
if at first you don't succeed try doing it the way your wife told you to
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
JosephP formula with corrected ranges
=SUMPRODUCT(LEN(B2:E2)-LEN(SUBSTITUTE(B2:E2,"*","")))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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 apologiesOriginally Posted by JosephP
no apology necessary! :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks