Counting the Contents of Two Columns
 

Hi

I'm setting up a spreadsheet and will have two columns of text... so for
instance columns A1:A5 could contain

Andy
Bob
Chris
Dave
Eddie

while columns D1:D5 could contain
Andy
Bill
Colin
Dave
Eric

What I want to do is find a way of doing a count of the number of
occurrences a name appears across BOTH columns. I can do it down a single
column with a Pivotable, but is there an easy way to do it down multiple
columns?

Hi
try
=COUNTIF(A1:A5,"Andy")+COUNTIF(D1:D5,"Andy")

--
Regards
Frank Kabel
Frankfurt, Germany

Molochi wrote:
Hi

I'm setting up a spreadsheet and will have two columns of text... so
for instance columns A1:A5 could contain

Andy
Bob
Chris
Dave
Eddie

while columns D1:D5 could contain
Andy
Bill
Colin
Dave
Eric

What I want to do is find a way of doing a count of the number of
occurrences a name appears across BOTH columns. I can do it down a
single column with a Pivotable, but is there an easy way to do it
down multiple columns?

Hiya Frank

The concept is good, very good in fact - the only problem is that the two
columns might run to hundreds of entries so I could do with some automatic
way of identifying the contents and then counting them.

I guess I could copy and past the two columns into a hidden third column and
Pivotable it, but I'm sure there must be an easier way?

"Frank Kabel" wrote:

Hi
try
=COUNTIF(A1:A5,"Andy")+COUNTIF(D1:D5,"Andy")

--
Regards
Frank Kabel
Frankfurt, Germany

Molochi wrote:
Hi

I'm setting up a spreadsheet and will have two columns of text... so
for instance columns A1:A5 could contain

Andy
Bob
Chris
Dave
Eddie

while columns D1:D5 could contain
Andy
Bill
Colin
Dave
Eric

What I want to do is find a way of doing a count of the number of
occurrences a name appears across BOTH columns. I can do it down a
single column with a Pivotable, but is there an easy way to do it
down multiple columns?

another view:
In Cell H1 enter =COUNTIF($D$1:$D$5,A1) and copy down to H5, then sum Col
H.
HTH
Jmay


"Frank Kabel" wrote in message
...
Hi
try
=COUNTIF(A1:A5,"Andy")+COUNTIF(D1:D5,"Andy")

--
Regards
Frank Kabel
Frankfurt, Germany

Molochi wrote:
Hi

I'm setting up a spreadsheet and will have two columns of text... so
for instance columns A1:A5 could contain

Andy
Bob
Chris
Dave
Eddie

while columns D1:D5 could contain
Andy
Bill
Colin
Dave
Eric

What I want to do is find a way of doing a count of the number of
occurrences a name appears across BOTH columns. I can do it down a
single column with a Pivotable, but is there an easy way to do it
down multiple columns?

Thanks Jim.

I think it's slightly clumsy, but using your way of doing it I've come up
with a solution:

=COUNTIF($A$1:$C$10,A1)

So long as column B doesn't contain any of the data I'm trying to count in
columns A or C I think I should be okay.

If anyone can come up with a more efficient way of doing it though, I'd
really appreciate it!

Hi
then use:
=COUNTIF(A:A,"Andy")+COUNTIF(D:D,"Andy")

--
Regards
Frank Kabel
Frankfurt, Germany

Molochi wrote:
Hiya Frank

The concept is good, very good in fact - the only problem is that the
two columns might run to hundreds of entries so I could do with some
automatic way of identifying the contents and then counting them.

I guess I could copy and past the two columns into a hidden third
column and Pivotable it, but I'm sure there must be an easier way?

"Frank Kabel" wrote:

Hi
try
=COUNTIF(A1:A5,"Andy")+COUNTIF(D1:D5,"Andy")

--
Regards
Frank Kabel
Frankfurt, Germany

Molochi wrote:
Hi

I'm setting up a spreadsheet and will have two columns of text... so
for instance columns A1:A5 could contain

Andy
Bob
Chris
Dave
Eddie

while columns D1:D5 could contain
Andy
Bill
Colin
Dave
Eric

What I want to do is find a way of doing a count of the number of
occurrences a name appears across BOTH columns. I can do it down a
single column with a Pivotable, but is there an easy way to do it
down multiple columns?

Why can't you just use sumproduct...
=sumproduct(--(a1:a10=d1:d10))

Wazooli

"Molochi" wrote:

Hi

I'm setting up a spreadsheet and will have two columns of text... so for
instance columns A1:A5 could contain

Andy
Bob
Chris
Dave
Eddie

while columns D1:D5 could contain
Andy
Bill
Colin
Dave
Eric

What I want to do is find a way of doing a count of the number of
occurrences a name appears across BOTH columns. I can do it down a single
column with a Pivotable, but is there an easy way to do it down multiple
columns?