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

 

[Zack Barresse]

somewhat, and sounds like we could take this in a couple of directions here, dependent upon your structure. could you post a sample copy of your data, and what you desire for results. i think that would help tremendously.

 

[santeria]

Okay, this is sample of A portion of one Day's data.
It's for April, because that is when they want to implement it.

Production April Call Quality.xls
	Q	R	S	T
6	INI.	CM	MSD	ADDV.
7	AH,AJ,AH,MM
8	AJ,AH
9	AL,AH,AL
10	MM,AL
11
12	AL
13
14	KG	4	8	0
15
16	MM
17
18
19
20	MM	1	0	0
April 1-2

At the bottom of each Daily Column is the Total Number of Each days Initials.

Then for the Entire Week:

Production April Call Quality.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
83	MONITORTOTALS	AL	2				AJ	0				MM	4
84		LD	1				KG	2				AH	2
April 1-2

This will sum up the number of occurences of each Initial. The indirect function is so that if the Staff concerned Change, then the Users of the Sheet simply just put in a New set of initials.
Ideally this count should Accurately sum up the total of Multi-initial Cells, as well as the cells that have maybe one or no initials in each cell.


Ta

 

[just_jon]

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

How about, with help from Morefunc --

Book1
	A	B	C	D
1	Data		Searchfor	Count
2	AA,BB,ZZ	CC,JJ	AA	6
3	AA,JJ,XX	UU-OO
4	XX	A,AA,AAAA
5	J
6	JJ	UU-OO
7	AA	JJ
Sheet1

D2: =(LEN(MCONCAT(A2:B7,","))-LEN(SUBSTITUTE(MCONCAT(A2:B7,","),C2,"")))/LEN(C2)

 

[santeria]

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

This looks excellent.

So for each Occurence of the Initial in C2, then I times the formula by 5 for the Formula to search over 5 days ??

Would that the best way to string this together, or is there a better way.
I think I can see what is happening, but I am not entirely sure on how to proceed and test.

TA

 

[Zack Barresse]

i'm slightly confused now, what would be the relevance of the extra columns? eg C:F, H:K, etc.

also, do you have to bunch your data up like that? in one cell?

 

[just_jon]

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

set-1

Book1
	A	B	C	D
1	Data
2	AA,BB,ZZ	CC,JJ
3	AA,JJ,XX	UU-OO
4	XX	A,AA,AAAA
5	J
6	JJ	UU-OO
7	AA	JJ
8
Sheet2

set-2

Book1
	A	B	C	D
1	Data
2	AA,BB,ZZ	CC,JJ
3
4
5
6
7
Sheet3

query

Book1
	A	B	C	D
1	Searchfor	Count
2	AA	7
3
Sheet1

 

[santeria]

If you mean in the Monitor Totals Area, nothing... thats just formatting, and can be changed.

The Data comes from the Columns in the Body of the 5 days summaries, as shown in the indirect formula.

The Monitor totals is currently a discretionary area to total the Initials.

Outside of that, I am trying to translate Just Jon's formula to the sheet.

I think I have it grasped.

It goes much further than what I have done, and looks totally brill.

Ta Muchly.

I still I have to see if I can add together five sets of columns and see what I come up with.

ray: ray:

 

[santeria]

Jon...

Have you come up with a new formula?

 

[santeria]

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?

 

[just_jon]

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

Added THREED to the MCONCAT so as to search multiple sheets.

You have to define the search array in the formula at the moment; don't know if MCONCAT/THREED take INDIRECT/INDIRECT.EXT ... [ How's that for expensive, Aladin!]