How to count data in 2 columns

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.

=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
>

=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
>
>

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

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
>

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...

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
>

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
>

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

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
>
>

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
>
>

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
>

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

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

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