I don't know if this is possible. I have a column that I enter
different chars. i.e. 1, d,hd,au,ad,L, SP, P. These are entered on
their own. But I need to be able to enter two different items in one
cell. Everything gets counted. Now, what I want to try and do is this.
If I enter a 1 and P in the same cell, can I get it to count the 1 and
the P seperately and put the total in different cells? I could have a
large number of entries in 1 column of any combinations and each need
to be counted on their own merit. I don't have enough columns to be
able to use 1 column for each char. Or, if this can't be done, is
there any way I can get round this?
Your wise advice would be appreciated.
Bryan.

Consider using AutoFiter:

Data Filter Autofilter Custom... Contains SP

This will hide all entries that do not contain SP. Finally use the
=SUBTOTAL() function to count the visible cells.

If the items in cells were separated by commas, then a macro could be
written to produce a complete tally for each individual code.

--
Gary''s Student - gsnu2007k


" wrote:

I don't know if this is possible. I have a column that I enter
different chars. i.e. 1, d,hd,au,ad,L, SP, P. These are entered on
their own. But I need to be able to enter two different items in one
cell. Everything gets counted. Now, what I want to try and do is this.
If I enter a 1 and P in the same cell, can I get it to count the 1 and
the P seperately and put the total in different cells? I could have a
large number of entries in 1 column of any combinations and each need
to be counted on their own merit. I don't have enough columns to be
able to use 1 column for each char. Or, if this can't be done, is
there any way I can get round this?
Your wise advice would be appreciated.
Bryan.

On 20 Nov, 10:18, Gary''s Student
wrote:
Consider using AutoFiter:

Data Filter Autofilter Custom... Contains SP

This will hide all entries that do not contain SP. *Finally use the
=SUBTOTAL() function to count the visible cells.

If the items in cells were separated by commas, then a macro could be
written to produce a complete tally for each individual code.

--
Gary''s Student - gsnu2007k



" wrote:
I don't know if this is possible. I have a column that I enter
different chars. i.e. 1, d,hd,au,ad,L, SP, P. These are entered on
their own. But I need to be able to enter two different items in one
cell. Everything gets counted. Now, what I want to try and do is this.
If I enter a 1 and P in the same cell, can I get it to count the 1 and
the P seperately and put the total in different cells? I could have a
large number of entries in 1 column of any combinations and each need
to be counted on their own merit. I don't have enough columns to be
able to use 1 column for each char. Or, if this can't be done, is
there any way I can get round this?
Your wise advice would be appreciated.
Bryan.- Hide quoted text -

- Show quoted text -

Gary, I like the idea of seperating with a comma, it can be any
combination of the various chars not just the SP & 1. What you suggest
sounds promising.
Maybe when I get to work I will post another message going into more
detail as to what I have already and the way it's working. Trouble is,
when I add something new to the workbook, someone comes up with the
bright idea to add a new feature that has to run with what has already
been done. So when I get to work I will post again. Thanks for your
input.
Bryan.

If you require further assistance on this topic, update this post.
--
Gary''s Student - gsnu200815


" wrote:

On 20 Nov, 10:18, Gary''s Student
wrote:
Consider using AutoFiter:

Data Filter Autofilter Custom... Contains SP

This will hide all entries that do not contain SP. Finally use the
=SUBTOTAL() function to count the visible cells.

If the items in cells were separated by commas, then a macro could be
written to produce a complete tally for each individual code.

--
Gary''s Student - gsnu2007k



" wrote:
I don't know if this is possible. I have a column that I enter
different chars. i.e. 1, d,hd,au,ad,L, SP, P. These are entered on
their own. But I need to be able to enter two different items in one
cell. Everything gets counted. Now, what I want to try and do is this.
If I enter a 1 and P in the same cell, can I get it to count the 1 and
the P seperately and put the total in different cells? I could have a
large number of entries in 1 column of any combinations and each need
to be counted on their own merit. I don't have enough columns to be
able to use 1 column for each char. Or, if this can't be done, is
there any way I can get round this?
Your wise advice would be appreciated.
Bryan.- Hide quoted text -

- Show quoted text -

Gary, I like the idea of seperating with a comma, it can be any
combination of the various chars not just the SP & 1. What you suggest
sounds promising.
Maybe when I get to work I will post another message going into more
detail as to what I have already and the way it's working. Trouble is,
when I add something new to the workbook, someone comes up with the
bright idea to add a new feature that has to run with what has already
been done. So when I get to work I will post again. Thanks for your
input.
Bryan.

On 20 Nov, 10:46, Gary''s Student
wrote:
If you require further assistance on this topic, update this post.
--
Gary''s Student - gsnu200815



" wrote:
On 20 Nov, 10:18, Gary''s Student
wrote:
Consider using AutoFiter:

Data Filter Autofilter Custom... Contains SP

This will hide all entries that do not contain SP. *Finally use the
=SUBTOTAL() function to count the visible cells.

If the items in cells were separated by commas, then a macro could be
written to produce a complete tally for each individual code.

--
Gary''s Student - gsnu2007k

" wrote:
I don't know if this is possible. I have a column that I enter
different chars. i.e. 1, d,hd,au,ad,L, SP, P. These are entered on
their own. But I need to be able to enter two different items in one
cell. Everything gets counted. Now, what I want to try and do is this.
If I enter a 1 and P in the same cell, can I get it to count the 1 and
the P seperately and put the total in different cells? I could have a
large number of entries in 1 column of any combinations and each need
to be counted on their own merit. I don't have enough columns to be
able to use 1 column for each char. Or, if this can't be done, is
there any way I can get round this?
Your wise advice would be appreciated.
Bryan.- Hide quoted text -

- Show quoted text -

Gary, I like the idea of seperating with a comma, it can be any
combination of the various chars not just the SP & 1. What you suggest
sounds promising.
Maybe when I get to work I will post another message going into more
detail as to what I have already and the way it's working. Trouble is,
when I add something new to the workbook, someone comes up with the
bright idea to add a new feature that has to run with what has already
been done. So when I get to work I will post again. Thanks for your
input.
Bryan.- Hide quoted text -

- Show quoted text -

Gary, thanks for the reply.
This may be hard to understand, but hopefully not too hard.
What I need to do is count the days planned absence or unplanned along
with 1’s for absence. The 1’s are counted both individually and in
blocks. If there are two 1’s single with a cell in between then it
will count as 2 and 2 separate occurrences. If the two 1’s are
together it will count 2 and 1 occurrence.
All these work really well. But I need to add either the P or UP to
count and produce the result in a different cell that where the 1’s
result is.
Below are the formulae that is used.
Any help would be great.

=HYPERLINK("#A!R6C"&MATCH(A9,A!$D$6:$IS$6,0)+3,"ju mp") to jump from
sheet (Home) to sheet (A)
=SUMPRODUCT(--(E6:E2037="d"))--(--SUMPRODUCT(--(E6:E2037="HD"))*0.5)
to count holidays full days & half days
=SUMPRODUCT(--(E6:E2037="L")) to count lateness
=SUMPRODUCT(--(E6:E2036=1),--(E7:E2037<1),--($A6:$A2036=TODAY
()-365)) to ignore any entry 365 days in the year before
=SUMPRODUCT(--(E6:E2037="AU"))--(--SUMPRODUCT(--(E6:E2037="AD"))*0.5)
to count authorised full days and half days.

Thanks in advance.

Bryan.