How to count data in 2 columns

[Ltat42a]

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]

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

--
HTH

Bob Phillips

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

 

[Guest]

=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

 

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

 

[Ltat42a]

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]

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

A1:A500
and
B1:B500
should work.

 

[Ltat42a]

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]

Yes, I will need a *unique* list of names. I will have about 20-2
persons in this list. In the column to the right of each name is
number (representig hours). Each of the persons listed will hav
multiple entries in this list. The results that I'm looking for i
"who" has "how many hours". If John Doe is in the list 50 times, eac
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

 

[Guest]

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]

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

Thanx

 

[RagDyeR]

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

message
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

 

[Bob Phillips]

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)

 

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

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

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]

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 th
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
daily or weekly basis - all year long.

Jim

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

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

I hope that helps.

Regards,
Ro

 

[Ron Coderre]

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]

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]

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]

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]

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

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

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

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

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

Does that help?

Regards,
Ro