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

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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


(y)
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
I think it would be easier to just make your own UDF something like
Code:
Public Function Countx(Rng as Range, Initials as String)

Dim Cell as Object
Dim Counter As Interger

    For Each Cell In Rng
        If Application.WorkSheetFunction.Find(Initials, Range(Cell.Address),1) > 0 Then
        Counter = Counter + 1
            Else
        End If
    Next x
Countx = Counter

End Function
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
So, the aim of using formula/s in the cells is totally out of the question?

A UDF/Macro would probably do it, but I was hoping to get a formula working, simply because the possibility seems to be there.

I can get a portion of the way there with formulas, and the use of indirect has been quite handy with staff changes, at many levels.

Seems that if I can keep the sheet working at the formula on the sheet level, then the flexibility for some other tasks I want to do ( such as keeping the data for vlookup displays) would be simpler.

I'm not saying a UDF would not be easy, simply that I would like to see if a Formula can be built to do it.

Ta Muchly.

(y)
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
If Aladin comes here I am sure he can come up with a formula using built in functions. It's beyond me, but a vba solution would be much simpler.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

hiya Santeria,

what is housed in B83?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
how about something like...

i know it's not shortened as much...

=sumproduct(--(b7:b81=b83)+--(g7:g81=b83)+--(l7:l81=b83)+--(Q7:q81=b83)+--(v7:v81=b83))

i'll probably get nailed by aladin too, but here it goes, hitting submit....
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844

ADVERTISEMENT

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

The Indirect references are all to initials.

It works fine with one initial per cell, but seems to bail on a series of initials.

If I can model this structure, I have some other applications for the formula, it just strikes me as feasible, but I can't seem to quite get there.

The Indirect element is necessary because the function needs to work by basic edits done by the Operations Managers.
Thats essentially idiot proofing it ... the more simpler I can build the sheet, then the more useful it is.

It's a bit like a Quiz thing I strung together... the thing works Nicely because the back end can be edited by anyone, and the Front end can be restructured to suit almost any purpose.

(y)
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Sorry, not clear.... Is this for the multiple initial search, or in place of my indirect Search for Initials ?

:unsure:


firefytr said:
how about something like...

i know it's not shortened as much...

=sumproduct(--(b7:b81=b83)+--(g7:g81=b83)+--(l7:l81=b83)+--(Q7:q81=b83)+--(v7:v81=b83))

i'll probably get nailed by aladin too, but here it goes, hitting submit....
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
in place of the indirect. it wouldn't handle multiples, not mine anyway.

if i understood your question, that is :confused:
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
It's basically about each cell in a series of columns handling a bunch of cells that each contain up to 6 sets of initials, which may occure more than once.

The difficulty is that counting single occurences of Initials is not too hard, but as soon as a second , third, and more, set of initials occur, the count fails.

The Indirect aspect was so that each set of initials could have an individual count. The First formula was the start of counting a Column of Initials, covering 74 cells at this stage, and then totalling just the number of initials, not saying how many occurred each time.

The second formula was about the weekly total for each set of initials.

I hope thats clear.

Ta

(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top