How to count data in 2 columns

**Ltat42a** · 05-29-2006, 12:30 PM

Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number.

Column A Coulmn B
John Doe 10
Jane Doe 8
Mary Doe 8
John Doe 8
John Doe 8

What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc...

In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc...

This listing will be extensive - several hundred rows long.
I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list.

Is this possible??

Thanx.

Bob Phillips · 05-29-2006, 12:45 PM

=SUMIF(A:A,"John Doe",B:B)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All, Can Excel count data that is in two columns? Example, I have a
> column of people's names. In the next column, I have a number.
>
> Column A Coulmn B
> John Doe 10
> Jane Doe 8
> Mary Doe 8
> John Doe 8
> John Doe 8
>
> What I'm looking for is a way to count the number of "John Doe's" and
> the number to the right of his name. Then the same for Mary Doe, Jane
> Doe, etc...etc...etc...
>
> In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> etc...etc...
>
> This listing will be extensive - several hundred rows long.
> I'm trying to summarize each instance of John Doe & all his numbers and
> so forth with everyone's name that is in the list.
>
> Is this possible??
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>

Toppers · 05-29-2006, 12:55 PM

=sumproduct(--(A1:A500)="John Doe"),--(B1:B100))

If "John Doe" is in a cell e.g C1, then replace literal by cell

=sumproduct(--(A1:A500)=C1),--(B1:B100))

HTH

"Ltat42a" wrote:

>
> Hi All, Can Excel count data that is in two columns? Example, I have a
> column of people's names. In the next column, I have a number.
>
> Column A Coulmn B
> John Doe 10
> Jane Doe 8
> Mary Doe 8
> John Doe 8
> John Doe 8
>
> What I'm looking for is a way to count the number of "John Doe's" and
> the number to the right of his name. Then the same for Mary Doe, Jane
> Doe, etc...etc...etc...
>
> In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> etc...etc...
>
> This listing will be extensive - several hundred rows long.
> I'm trying to summarize each instance of John Doe & all his numbers and
> so forth with everyone's name that is in the list.
>
> Is this possible??
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>
>

Ragdyer · 05-29-2006, 01:25 PM

Since you say that the list is extensive, you could simply return the total
along side *every* name on the list, so that you'll have duplicate data, but
you wouldn't need to create a separate list of names, and you'll be sure to
display the totals for all names in the list.

=Sumif($A$1:$A$1000,A1,$B$1:$B$1000)

And copy down.

If you *need* to create a *unique* list of all names, with the totals along
side, post back.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All, Can Excel count data that is in two columns? Example, I have a
> column of people's names. In the next column, I have a number.
>
> Column A Coulmn B
> John Doe 10
> Jane Doe 8
> Mary Doe 8
> John Doe 8
> John Doe 8
>
> What I'm looking for is a way to count the number of "John Doe's" and
> the number to the right of his name. Then the same for Mary Doe, Jane
> Doe, etc...etc...etc...
>
> In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> etc...etc...
>
> This listing will be extensive - several hundred rows long.
> I'm trying to summarize each instance of John Doe & all his numbers and
> so forth with everyone's name that is in the list.
>
> Is this possible??
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>

**Ltat42a** · 05-29-2006, 03:44 PM

Thanx for the assistance.

This worked for me - =SUMIF(A:A,"John Doe",B:B)

This didn't -
=sumproduct(--(A1:A500)="John Doe"),--(B1:B100))

If "John Doe" is in a cell e.g C1, then replace literal by cell

=sumproduct(--(A1:A500)=C1),--(B1:B100))

When I used these, I kept getting the #VALUE! error. My list of names
is in A2:A60, the corresponding numbers are in B2:B60.

I'm using Excel 2003.

Thanx

Ragdyer · 05-29-2006, 04:10 PM

Toppers had a typo in the SumProduct formula.
*All* ranges must be of equal size.

A1:A500
and
B1:B500
should work.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Thanx for the assistance.
>
> This worked for me - =SUMIF(A:A,"John Doe",B:B)
>
> This didn't -
> =sumproduct(--(A1:A500)="John Doe"),--(B1:B100))
>
> If "John Doe" is in a cell e.g C1, then replace literal by cell
>
> =sumproduct(--(A1:A500)=C1),--(B1:B100))
>
> When I used these, I kept getting the #VALUE! error. My list of names
> is in A2:A60, the corresponding numbers are in B2:B60.
>
> I'm using Excel 2003.
>
> Thanx
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>

**Ltat42a** · 05-29-2006, 04:25 PM

Still no luck, keep getting the #value! error.

My data is in the correct columns, the formula is looking in the correct columns, but I keep getting the value error.

Thanx again for the reply...

**Ltat42a** · 05-29-2006, 08:14 PM

Yes, I will need a *unique* list of names. I will have about 20-27 persons in this list. In the column to the right of each name is a number (representig hours). Each of the persons listed will have multiple entries in this list. The results that I'm looking for is "who" has "how many hours". If John Doe is in the list 50 times, each time he is listed, he has 2 hours. My result will be "John Doe" = 100hrs. This will repeat for each person in the list.

Does this help??

Thanx

Originally Posted by Ragdyer

Since you say that the list is extensive, you could simply return the total
along side *every* name on the list, so that you'll have duplicate data, but
you wouldn't need to create a separate list of names, and you'll be sure to
display the totals for all names in the list.

=Sumif($A$1:$A$1000,A1,$B$1:$B$1000)

And copy down.

If you *need* to create a *unique* list of all names, with the totals along
side, post back.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All, Can Excel count data that is in two columns? Example, I have a
> column of people's names. In the next column, I have a number.
>
> Column A Coulmn B
> John Doe 10
> Jane Doe 8
> Mary Doe 8
> John Doe 8
> John Doe 8
>
> What I'm looking for is a way to count the number of "John Doe's" and
> the number to the right of his name. Then the same for Mary Doe, Jane
> Doe, etc...etc...etc...
>
> In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> etc...etc...
>
> This listing will be extensive - several hundred rows long.
> I'm trying to summarize each instance of John Doe & all his numbers and
> so forth with everyone's name that is in the list.
>
> Is this possible??
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>

Ron Coderre · 05-29-2006, 08:25 PM

Have you ruled out Pivot Tables?
A Pivot Table would automatically list each unique name and present the
total values for each name.

Is that something you'd be interested in?
***********
Regards,
Ron

XL2002, WinXP

"Ltat42a" wrote:

>
> Hi All, Can Excel count data that is in two columns? Example, I have a
> column of people's names. In the next column, I have a number.
>
> Column A Coulmn B
> John Doe 10
> Jane Doe 8
> Mary Doe 8
> John Doe 8
> John Doe 8
>
> What I'm looking for is a way to count the number of "John Doe's" and
> the number to the right of his name. Then the same for Mary Doe, Jane
> Doe, etc...etc...etc...
>
> In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> etc...etc...
>
> This listing will be extensive - several hundred rows long.
> I'm trying to summarize each instance of John Doe & all his numbers and
> so forth with everyone's name that is in the list.
>
> Is this possible??
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>
>

**Ltat42a** · 05-29-2006, 09:40 PM

No, I haven't ruled them out. I haven't used pivot tables before.
Yes...I am interested.

Thanx

Originally Posted by Ron Coderre

Have you ruled out Pivot Tables?
A Pivot Table would automatically list each unique name and present the
total values for each name.

Is that something you'd be interested in?
***********
Regards,
Ron

XL2002, WinXP

"Ltat42a" wrote:

>
> Hi All, Can Excel count data that is in two columns? Example, I have a
> column of people's names. In the next column, I have a number.
>
> Column A Coulmn B
> John Doe 10
> Jane Doe 8
> Mary Doe 8
> John Doe 8
> John Doe 8
>
> What I'm looking for is a way to count the number of "John Doe's" and
> the number to the right of his name. Then the same for Mary Doe, Jane
> Doe, etc...etc...etc...
>
> In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> etc...etc...
>
> This listing will be extensive - several hundred rows long.
> I'm trying to summarize each instance of John Doe & all his numbers and
> so forth with everyone's name that is in the list.
>
> Is this possible??
>
> Thanx.
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>
>

RagDyeR · 05-30-2006, 02:44 AM

Say your list of names is in A1 to A100,
and the hours are in B1 to B100.

In C1, enter
=A1

In C2, enter this *array* formula:

=IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$100&""),0)),"",INDEX(IF(ISBLANK($
A$1:$A$100),"",A$1:$A$100),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$100&""),0)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* committing the formula in C2 using *CSE*, drag down to copy as many
rows as you anticipate you'll need to display all your unique names.

THEN, in D1, enter this formula:

=IF(C1<>"",SUMIF($A$1:$A$100,C1,$B$1:$B$100),"")

And drag down to copy as far as you have formulas (names) in Column C.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...

Yes, I will need a *unique* list of names. I will have about 20-27
persons in this list. In the column to the right of each name is a
number (representig hours). Each of the persons listed will have
multiple entries in this list. The results that I'm looking for is
"who" has "how many hours". If John Doe is in the list 50 times, each
time he is listed, he has 2 hours. My result will be "John Doe" =
100hrs. This will repeat for each person in the list.

Does this help??

Thanx

Ragdyer Wrote:
> Since you say that the list is extensive, you could simply return the
> total
> along side *every* name on the list, so that you'll have duplicate
> data, but
> you wouldn't need to create a separate list of names, and you'll be
> sure to
> display the totals for all names in the list.
>
> =Sumif($A$1:$A$1000,A1,$B$1:$B$1000)
>
> And copy down.
>
> If you *need* to create a *unique* list of all names, with the totals
> along
> side, post back.
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit
> !
> --------------------------------------------------------------------------
-
>
> "Ltat42a" <[email protected]> wrote
> in
> message news:[email protected]...
> >
> > Hi All, Can Excel count data that is in two columns? Example, I have
> a
> > column of people's names. In the next column, I have a number.
> >
> > Column A Coulmn B
> > John Doe 10
> > Jane Doe 8
> > Mary Doe 8
> > John Doe 8
> > John Doe 8
> >
> > What I'm looking for is a way to count the number of "John Doe's"
> and
> > the number to the right of his name. Then the same for Mary Doe,
> Jane
> > Doe, etc...etc...etc...
> >
> > In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> > etc...etc...
> >
> > This listing will be extensive - several hundred rows long.
> > I'm trying to summarize each instance of John Doe & all his numbers
> and
> > so forth with everyone's name that is in the list.
> >
> > Is this possible??
> >
> > Thanx.
> >
> >
> > --
> > Ltat42a
> >
> ------------------------------------------------------------------------
> > Ltat42a's Profile:
> http://www.excelforum.com/member.php...o&userid=24735
> > View this thread:
> http://www.excelforum.com/showthread...hreadid=546477
> >

--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=546477

Bob Phillips · 05-30-2006, 03:45 AM

See http://www.contextures.com/xlPivot01.html
and http://www.peltierteh.com/Excel/Pivots/pivottables.htm

for some good intros to pivot tables.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> No, I haven't ruled them out. I haven't used pivot tables before.
> Yes...I am interested.
>
> Thanx
>
>
>
> Ron Coderre Wrote:
> > Have you ruled out Pivot Tables?
> > A Pivot Table would automatically list each unique name and present
> > the
> > total values for each name.
> >
> > Is that something you'd be interested in?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Ltat42a" wrote:
> >
> > >
> > > Hi All, Can Excel count data that is in two columns? Example, I have
> > a
> > > column of people's names. In the next column, I have a number.
> > >
> > > Column A Coulmn B
> > > John Doe 10
> > > Jane Doe 8
> > > Mary Doe 8
> > > John Doe 8
> > > John Doe 8
> > >
> > > What I'm looking for is a way to count the number of "John Doe's"
> > and
> > > the number to the right of his name. Then the same for Mary Doe,
> > Jane
> > > Doe, etc...etc...etc...
> > >
> > > In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8,
> > > etc...etc...
> > >
> > > This listing will be extensive - several hundred rows long.
> > > I'm trying to summarize each instance of John Doe & all his numbers
> > and
> > > so forth with everyone's name that is in the list.
> > >
> > > Is this possible??
> > >
> > > Thanx.
> > >
> > >
> > > --
> > > Ltat42a
> > >
> > ------------------------------------------------------------------------
> > > Ltat42a's Profile:
> > http://www.excelforum.com/member.php...o&userid=24735
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=546477
> > >
> > >
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=546477
>

**Ron Coderre** · 05-30-2006, 08:32 AM

Ltat42a

Here's a jumpstart on the Pivot Table process:

First, make sure your data table has column titles (EmpName, Amount, etc)

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the EmpName field here
DATA: Drag the Amount field here
If it doesn't list as Sum of Amount...dbl-click it and set it to Sum.
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each EmpName and the sum of their respective amounts.

To refresh the Pivot Table, just right click it and select Refresh Data

To learn about the other options Pivot Tables offer, check the links that Bob Phillips posted.

I hope that helps.

Regards,
Ron

**Ltat42a** · 05-31-2006, 08:47 AM

Thanx Ron...I think that will work much better than trying to use sums & counts formulas for each person. The only thing I need to figure out is, the list of names and hours will be constantly growing. Once I make the pivot table, do I have to re-make it again if I add to the data?

The pivot table I made resides on a different worksheet than the data.
The data worksheet will continue to grow with names and hours on a daily or weekly basis - all year long.

Jim

Originally Posted by Ron Coderre

Ltat42a

Here's a jumpstart on the Pivot Table process:

First, make sure your data table has column titles (EmpName, Amount, etc)

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the EmpName field here
DATA: Drag the Amount field here
If it doesn't list as Sum of Amount...dbl-click it and set it to Sum.
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each EmpName and the sum of their respective amounts.

To refresh the Pivot Table, just right click it and select Refresh Data

To learn about the other options Pivot Tables offer, check the links that Bob Phillips posted.

I hope that helps.

Regards,
Ron

**Ltat42a** · 05-31-2006, 08:52 AM

Thanx Ron...I think that will work much better than trying to use sums & counts formulas for each person. The only thing I need to figure out is, the list of names and hours will be constantly growing. Once I make the pivot table, do I have to re-make it again if I add to the data?

The pivot table I made resides on a different worksheet than the data.
The data worksheet will continue to grow with names and hours on a daily or weekly basis - all year long.

Jim

Originally Posted by Ron Coderre

Ltat42a

Here's a jumpstart on the Pivot Table process:

First, make sure your data table has column titles (EmpName, Amount, etc)

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the EmpName field here
DATA: Drag the Amount field here
If it doesn't list as Sum of Amount...dbl-click it and set it to Sum.
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each EmpName and the sum of their respective amounts.

To refresh the Pivot Table, just right click it and select Refresh Data

To learn about the other options Pivot Tables offer, check the links that Bob Phillips posted.

I hope that helps.

Regards,
Ron

**Ron Coderre** · 05-31-2006, 09:12 AM

Ltat42a

If you base your Pivot Table on a Dynamic Range Name, it will automatically include any additions to the list when you refresh the Pivot Table.

For really good instructions on how to create a Dynamic Range Name, see Debra Dalgleish's website:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?

Regards,
Ron
(This may be a dbl-post...the first try bounced to an error screen)

**Ltat42a** · 05-31-2006, 12:01 PM

Hey Ron, I read through the tutorials about naming ranges and dynamic data source.

The spreadsheet that I'm using is exported from a table query in Access.
This spreadsheet has the employee names (Col A) and hours (Col B) listed.

When I create the pivot table based on the range of names and hours, it works pretty good. If I add to this list - the pivot table does not update.

Right now, in Col A & Col B, my list goes down to row 25. I will be adding to these columns through out the year. I may have as many as 300 rows - but only the two columns.

I've tried & tried to get the pivot table to update, when I add rows to these two columns, the table does not update, I'm baffled.

Any Suggestions?

Thanx...Jim

**Ron Coderre** · 05-31-2006, 12:08 PM

Jim

You'll need to use a Dynamic Range Name, as described in my previous post. A Dynamic Range Name automatically resizes as the data area grows or shrinks.

Follow the instructions at the Contextures website: http://www.contextures.com/xlNames01.html#Dynamic

and post back if you have any questions.

Regards,
Ron

**Ltat42a** · 05-31-2006, 12:33 PM

Here's a rundown of what I have:

Excel 2003

In my spreadsheet, I have only one worksheet (tbl_hours_query)
In this worksheet, A1 = my header name "Employee", B1 = "Hours".
A2: A25 = all my names, B2:B25 = hours for these employees.

I highligthed A1:A25, named the range "Employee"

I highlighted B1:B25, named the range "Hours"

Here's where I'm a lil baffled. I want to use a dynamic data source,
says to Insert, Name, Define, then insert the offset formula which I do.

Do I do this in the same worksheet as the data, or in a new worksheet?

I use this for the "refers To:"
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),2)

Note: I removed the "Data!" when I do this in the same worksheet as my list, I made a new worksheet, and inserted the worksheet name.

If I do either of these then add in the pivot table, the pivot table does not update when I add names and hours to the list.

I'm gonna get this, it will not defeat me!!!!!!!!!!!

Thanx.

**Ron Coderre** · 05-31-2006, 12:48 PM

Ok, Jim....I think we got it this time.

On the list worksheet...
<insert><name><define>
Names in workbook: rngPivotSource
Refers to: =OFFSET($A$1,0,0,COUNTA(Data!$A:$A),2)

That will create the Dynamic Range Name that you will use in the Pivot Table.

(You don't need separate ranges named Employees or Hours for the Pivot Table. The one rngPivotSource range will suffice)

I think you may have gotten that far ok....but, the confusion may be with the way Pivot Tables work.

The reason the data displayed in a Pivot Table doesn't change unless you REFRESH the Pivot Table is this. Pivot Tables use a hidden, internal COPY of the source data. Doing that freezes the data set so can analyze a static data set, even if the source data is changing. Refreshing the Pivot Table replaces the internal data cache with the latest information.

So, after pulling new data, deleting rows, or adding rows....refresh the Pivot Table to see the latest data.

Does that help?

Regards,
Ron

**Ltat42a** · 05-31-2006, 12:55 PM

Hey Ron....think I got it working. I think the mistake I was making was that I was creating the "Insert, Name, Define + offset formula" for both the employee column and the hours column. Every time I insert new names and hours, the pivot table was not updating. I deleted everything in "Insert...Names......" just inserted the "Employee name with the offset formula, changed the last numeral to "2" (2 columns), and it seems to be working.

I made the pivot table appear in a new worksheet, added names and hours to the list, updated the pivot table and it seemed to update fine.

Thanx for all the help (and patience!). It's definetly a nice little tool we'll be using for quite some time.

Thanx again...Jim