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)
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
Re: COUNT-ing Multiple text events in a Cell, and over a Col

hiya Santeria,

what is housed in B83?
 
Upvote 0
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....
 
Upvote 0
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)
 
Upvote 0
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....
 
Upvote 0
in place of the indirect. it wouldn't handle multiples, not mine anyway.

if i understood your question, that is :confused:
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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
Back
Top