COUNT-ing Multiple text events in a Cell, and over a Column.

[santeria]

I think I have found just about every permutation of how to count a Cell content when the cell has text in it.
Thing is, I can't seem to find something that will count multiple occuruences of text.

the basic

=COUNTIF(B7:B81,"**")

Covers a sum up of Cells in a Column, where each cell has only one entry, in this case, and entry of an Initial.

and

=SUM((COUNTIF($B$7:$B$81,INDIRECT("B83"))+(COUNTIF($G$7:$G$81,INDIRECT("B83"))+COUNTIF($L$7:$L$81,INDIRECT("B83"))+COUNTIF($Q$7:$Q$81,INDIRECT("B83"))+COUNTIF($V$7:$V$81,INDIRECT("B83")))))

Covers me for specific Initials that occur over a week.

However, Some columns will have cells that have either


AJ,CJ,MM,AH

or

AJ 031904,CJ 031904,MM 031904,AH 031904

Down the entire row.


The Countif formula is what I basically want to do for each Column, wether it is purely initials, or Initials plus date.

And for the Week, I need to Sum Up Initials for each available initial ( currenly 6 variations), and whilst I am looking for Initials at this time, the initials plus the date of the initials entry is also the main possibility.

I suspect an array formula is required, but I can't conceive of how this would be constructed.
But, if a non-array formula is contsructable, then that would work just as well.


Ta

 

[santeria]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

It's the Columns I am trying for...

I Tried


=LEN(B7:B81)-LEN(SUBSTITUTE(B7:B81,"AL",""))

Which gives a Value error
And ...
if I use

=LEN(B21)-LEN(SUBSTITUTE(B21,"AL",""))

The answer is 6 even though the AL initial only occurs 3 times.

So, I am still as Confused as Hell, but feel closer since at least the formula is adding up the total number of letters for all three occurences.

 

[just_jon]

If I model the Formula as :

=(LEN(MCONCAT(B7:B81,","))-LEN(SUBSTITUTE(MCONCAT(B7:B81,","),C83,"")))/LEN(C83)

Searching for Initals in C83, is this how the formula Works?
Or Have I misunderstood... it does not seem to be adding the Cells if I do them column by column...

Sorry, I am not sure what I have done wrong?

And what did you get? What was expected? The above searches for instances of C83 in B7 thru B81

 

[santeria]

Bingo !!!

If I change/Correct it to:

=(LEN(MCONCAT(B7:B81,","))-LEN(SUBSTITUTE(MCONCAT(B7:B81,","),B83,"")))/LEN(B83)

Gets 4, the Right Value.

The thing I have to correct for now is a Search through Multiple Columns

You are so Brilliant Just Jon !!!

Works a Treat... I just have to do this final tweak.

Ta Muchly.

ray: ray: ray:

 

[santeria]

Okay,

=(LEN(MCONCAT(B7:B81,","))-LEN(SUBSTITUTE(MCONCAT(B7:B81,","),B83,"")))/LEN(B83)


Works a Treat.
How do you change the B83 instance to a Wildcard, so that it will search for the Number of Initials in the Column, instead of a specific Inital... I have tried some variations , but i am not clear on this.


Ta

 

[just_jon]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

An improved formula --

=SUMPRODUCT(LEN(","&$B$7:$B$81&",")-LEN(SUBSTITUTE(","&$B$7:$B$81&",",B83,"")))/LEN(B83)


which will count instances of B83 in the range B7 thru B81

 

[santeria]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

Thanks.

This
=(LEN(MCONCAT(B7:B81,","))-LEN(SUBSTITUTE(MCONCAT(B7:B81,","),B83,"")))/LEN(B83)

Does the Same as:
=SUMPRODUCT(LEN(","&$B$7:$B$81&",")-LEN(SUBSTITUTE(","&$B$7:$B$81&",",B83,"")))/LEN(B83)


The Curly is the Variable, and how to add the Day Columns Together ( well second part is not so curly just lengthy, and it's working in separate cells.), just the wildcard element that is something that would be cool.


You are a Bon a Fide Genius !!!!!!!!!

Ta a Bazillion times over !!!!!!

ray: ray: ray: ray:

 

[santeria]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

Basically, this is, with some modications for cell references, what works for the weekly formulas:


=(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),B83,"")))/LEN(B83)+(LEN(MCONCAT($G$7:$G$81,","))-LEN(SUBSTITUTE(MCONCAT($G$7:$G$81,","),B83,"")))/LEN(B83)+(LEN(MCONCAT($L$7:$L$81,","))-LEN(SUBSTITUTE(MCONCAT($L$7:$L$81,","),B83,"")))/LEN(B83)+(LEN(MCONCAT($Q$7:$Q$81,","))-LEN(SUBSTITUTE(MCONCAT($Q$7:$Q$81,","),B83,"")))/LEN(B83)+(LEN(MCONCAT($V$7:$V$81,","))-LEN(SUBSTITUTE(MCONCAT($V$7:$V$81,","),B83,"")))/LEN(B83)






Ta

 

[santeria]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

Now, if only I understood why the thing works

 

[santeria]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

Okay,
This charmingly brief formula adds the total number of Initials, variation of 6 initials...

=(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),AE5,"")))/LEN(AE5)+(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),AF5,"")))/LEN(AF5)+(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),AE11,"")))/LEN(AE11)+(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),AF11,"")))/LEN(AF11)+(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),AE17,"")))/LEN(AE17)+(LEN(MCONCAT($B$7:$B$81,","))-LEN(SUBSTITUTE(MCONCAT($B$7:$B$81,","),AF17,"")))/LEN(AF17)


Over the Course of a Day.

Two things I am looking for, can this be cut down to a Wildcard Search for a Variety of initials?

And/Or,

if an initial has a an occurence like, KG, and to save it being entered 6 times, the initials are put in as "KG(6)", and each Set of initials has a Value in brackets after it, can this formula work to total the initials, and the number beside it over the course of the 5 columns ?

Do-able ??
I can't think how, but if you can, then I would appreciate it.


Ta

 

[just_jon]

Re: COUNT-ing Multiple text events in a Cell, and over a Col

What are the values of AE5, AF5, AE11, AF11, AE17 and AF17? Are these the only ones you would ever want to search for?