Count references within a cell

  • Thread starter Thread starter Al Mackay
  • Start date Start date
A

Al Mackay

Would anyone be able to help with the following.

I'm trying to count entries within a single cell, e.g. Cell A1 may
contain the following

nnnyynnyynynyny

(these are all responses to information and have to be, unfortunately,
recorded within the one cell).

Therefore what I want to be able to do is then have a separate column
that will say:

Count every 1st character within range (A1:G1) if not equal to N - and
then there will be a second column that will do the same for the 2nd
character etc. etc. (altogether, there will be 15 entries which will
be counted across column A to G).

Is this possible - I've been messing with the left function, combining
it with countif / counta etc. to no avail.

appreciate your help on this.

Cheers, Al. ( (e-mail address removed) )
 
Hi
try the following in A1 on a separate sheet
=SUMPRODUCT(--(MID('sheet1'!$A$1:$G$1,COLUMN(),1)="n"))
and copy to the right
 
Al,

Is this what you want

=SUMPRODUCT(--(MID(A1:G1,1,1)="n"))

Change the first 1 to the character position, 1st, 2nd, 3rd, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob / Frank

Thanks for your help on this - this is really appreciated.

Tried the example below and this worked brilliantly. Would you be
able to advise how this works? e.g. I understand Sumproduct as I've
used this function before, but not sure what the following part does:
(--

also understand the mid function however not quite sure what the
second 1 does in the example below?

Would be helpful incase I get a similar problem to deal with in the
future and to help me to understand what the solution does.

Many thanks again - Al.
 
Hi Al,

Let's take a shot at this.

The MID function, MID(A1:G1,1,1), is getting one character (the second 1)
from the first character (the first 1) in the cell being addressed, and then
comparing it against your vale, the letter n.

Because we are using SUMPRODUCT, we can use a range and it will iterate
through each cell in the range. When any or each of the cells in the
SUMPRODUCT test matches, that is if the first character in A1, B1, C1, etc.
is an n or not, then it returns a True/False value. In itself, this is
useless to us when SUMming, we need numbers, so we coerce to a 1/0, which is
what the -- does. Basically we double negate it to coerce a boolean value to
a numeric. We do it twice else it would be negative.

We could just as validly use N =SUMPRODUCT(N(MID(A1:G1,1,1)="n"))
or mulitiply by 1 =SUMPRODUCT(1*(MID(A1:G1,1,1)="n"))
but -- is rapidly becoming the accepted way in these NGs.

By the way, I could have used LEFT(A1:G1,1), but this only applies for the
first character, the second, third etc. would need MID, so I used it for
all.

So the first character is tested with

=SUMPRODUCT(--(MID(A1:G1,1,1)="n")) or
=SUMPRODUCT(N(MID(A1:G1,1,1)="n")) or
=SUMPRODUCT(1*(MID(A1:G1,1,1)="n")) or even
=SUMPRODUCT(--(LEFT(A1:G1,1)="n"))

the second is tested with

=SUMPRODUCT(--(MID(A1:G1,2,1)="n")) or
=SUMPRODUCT(N(MID(A1:G1,2,1)="n")) or
=SUMPRODUCT(1*(MID(A1:G1,2,1)="n"))

etc.

Oh by the way, just to complicate it, if you wanted to test either the 1st,
second or 3rd character for n you could use

Does that help or hinder?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top