| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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? |
|
#2
|
|||
|
|||
|
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? |
|
#3
|
|||
|
|||
|
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? |
|
#4
|
|||
|
|||
|
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? |
|
#5
|
|||
|
|||
|
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! |
|
#6
|
|||
|
|||
|
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? |
|
#7
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I select a range if one cells contents is equal to another | Excel Discussion (Misc queries) | |||
| Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) | |||
| interchange columns with rows | Excel Discussion (Misc queries) | |||
| How can I find the common names in two columns of names? | Excel Discussion (Misc queries) | |||
| How do I merge the contents of two columns? | Excel Discussion (Misc queries) | |||
Linear Mode
