Extended COUNT logic

  • Thread starter Thread starter Mr. Smith
  • Start date Start date
M

Mr. Smith

Hi.

Users are able to enter a txt code (i.e Gov, Org, Priv) in a range B10:AB10.
In cell A10 I'm counting the codes using =COUNTA(B10:AB10). But now I want
to allow my users to enter more than one code in each cell. The COUNTA
formula will however not count more than one instance in one cell.

How can I get Excel to return COUNT = 3 if cell B10 containts 'Gov.' and
cell C10 conatins 'Gov. / Org.' Using / as an fixed separator between values
within a cell?

Psaudo something like this...
=COUNTA(B10:AB10)+COUNTIF(B10:AB10;/;"")

Problem is that the COUNTIF statement only return true if the cell value is
'/' and not if it's Gov./Org.

Rgrds
Mr. Smith
 
You could use the following array formula:

=SUM(IF(LEN(E1:E8)>0,1+LEN(E1:E8)-LEN(SUBSTITUTE(E1:E8,"/","")),0))

You need to change the range to suit and enter with Ctrl-Shift-Enter t
get it to process as an array formula. You need to be careful whe
using array formulas that they are re-entered correctly after an edi
otherwise you will get unexpected results.

T
 
Not entirely clear what you are asking. Do you want to count
occurrences of specific codes, as in

=SUMPRODUCT(ISNUMBER(FIND("Gov.",B10:AB10))*1)+SUMPRODUCT(ISNUMBER(FIND("Org.",B10:AB10))*1)

or occurrences of anything, as in

=COUNTA(B10:AB10)+SUMPRODUCT(ISNUMBER(FIND("/",B10:AB10))*1)

Jerry
Excel MVP
 
To extend the second formula to allow three codes, you would use

=COUNTA(B10:AB10)+SUMPRODUCT(ISNUMBER(FIND("/",B10:AB10))*1)
+SUMPRODUCT(ISNUMBER(FIND("/",B10:AB10,FIND("/",B10:AB10)+1))*1)

Jerry
 
Thanks tinyjack, Frank and Jerry W.

I found Jerry's formula usefull, since I do not currently need to allow more
than two codes in each cell.

Thanks again.

Mr. Smith
 
Hi.

Users are able to enter a txt code (i.e Gov, Org, Priv) in a range B10:AB10.
In cell A10 I'm counting the codes using =COUNTA(B10:AB10). But now I want
to allow my users to enter more than one code in each cell. The COUNTA
formula will however not count more than one instance in one cell.

How can I get Excel to return COUNT = 3 if cell B10 containts 'Gov.' and
cell C10 conatins 'Gov. / Org.' Using / as an fixed separator between values
within a cell?

Psaudo something like this...
=COUNTA(B10:AB10)+COUNTIF(B10:AB10;/;"")

Problem is that the COUNTIF statement only return true if the cell value is
'/' and not if it's Gov./Org.

Rgrds
Mr. Smith
Click to expand...

Hmmm. How about: =COUNTIF(C3:C11,"*/*")+COUNTA(C3:C11)

Note the wild card in the COUNTIF.


--ron
 
Back
Top