I have a worksheet that has multiple rows that have the same name. For example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed. Similar
to a vlookup but able to know that if I want the 401k column it should return
400 for Patrick after finding his name twice and summing that column. If I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

On Jan 12, 12:54*pm, Julie wrote:
I have a worksheet that has multiple rows that have the same name. For example.

I have a worksheet that looks like this
Name * * * * 401k * * * * FICA
Julie * * * * * 100 * * * * * 200
Patrick * * * *200 * * * * *400
Sam * * * * * *50 * * * * * 100
Julie * * * * * 100 * * * * * 200
Julie * * * * * 100 * * * * * 200
Patrick * * * *200 * * * * * 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed. Similar
to a vlookup but able to know that if I want the 401k column it should return
400 for Patrick after finding his name twice and summing that column. *If I
wanted the FICA column it would return 800 for that column. *

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

You want =SUMIF(
Check the help for constructing your ranges.
Pierre

Hi Pierre,

Try this.
With your names in E5:E10, the 401k values in F5:F10 and FICA values in
G5:G10.

Select the 401k values and name that range, I used K401K. (401k won't work
for a name). Select and name the FICA values, I used Fica.

In cell H1 produce a data validation drop down using List and in the source
box enter K401K,FICA. (Note the coma.) OK out.

In G1 produce a drop down using list and in the source box enter the names
Julie,Patrick,Sam. OK out.

In a preferred cell enter =SUMIF(E5:E10,G1,INDIRECT(H1))

Select a name in G1 and K401K or FICA in H1 to see sums.

HTH
Regards,
Howard

"Pierre" wrote in message
...
On Jan 12, 12:54 pm, Julie wrote:
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column. If I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

You want =SUMIF(
Check the help for constructing your ranges.
Pierre

OK so what if I wanted to add a column for last names after the name column?
Right now I have the formula reading:
=SUMIF(A1:A6,"Patrick",B1:B6)

If I have 2 patricks how can I expand the criteria to only return Patrick
Smith? I don't know how to add the second criteria. Please help!


"Pierre" wrote:

On Jan 12, 12:54 pm, Julie wrote:
I have a worksheet that has multiple rows that have the same name. For example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed. Similar
to a vlookup but able to know that if I want the 401k column it should return
400 for Patrick after finding his name twice and summing that column. If I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

You want =SUMIF(
Check the help for constructing your ranges.
Pierre
.

Hi Julie,

(I replied to Pierre by mistake but do not see that post yet)

Try this.
With your names in E5:E10, the 401k values in F5:F10 and FICA values in
G5:G10.

Select the 401k values and name that range, I used K401K. (401k won't work
for a name). Select and name the FICA values, I used Fica.

In cell H1 produce a data validation drop down using List and in the source
box enter K401K,FICA. (Note the coma.) OK out.

In G1 produce a drop down using list and in the source box enter the names
Julie,Patrick,Sam. OK out.

In a preferred cell enter =SUMIF(E5:E10,G1,INDIRECT(H1))

Select a name in G1 and K401K or FICA in H1 to see sums.

HTH
Regards,
Howard

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column. If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Hi Julie,

Try this formula in conjunction of my previous post, where G1 is a drop down
of first names and G2 is a drop down of Last names.

First names in D5:D10, last names are in E5:E10
401k values in F5:F10, Fica in G5:G10
H1 is a drop down to select either K401K or FICA

=SUM(IF(D5:D10=G1,IF(E5:E10=G2,INDIRECT(H1,0),0)))

Enter using CTRL + SHIFT + ENTER. You will ge { } around the formula.

HTH
Regards,
Howard

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column. If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Hi,

You may create a pivot table. Drag Name to the row area and 401k, FICA to
the data area. The pivot will auto summarise your data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column. If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Ashish,

I don't want to use a pivot table because I was told I would have to
recreate the pivot table every time I wanted to update the spreadsheet. I
will be using this formula every week on new data and want to write something
that I can simply refresh.

Julie

"Ashish Mathur" wrote:

Hi,

You may create a pivot table. Drag Name to the row area and 401k, FICA to
the data area. The pivot will auto summarise your data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column. If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Hi,

You would not have to recreate the pivot everytime you add date by rows to
the existing database. Simply select the range (including the header row)
and convert it to a List(Excel 2003)/Table(Excel 2007). A table make the
database auto expanding. Now when you add data by rows, jut right click
on the pivot and Refresh (this will consider the new rows added).

If you anyways wish to work with formulas, then I think you have got the
desired solution from others.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
Ashish,

I don't want to use a pivot table because I was told I would have to
recreate the pivot table every time I wanted to update the spreadsheet. I
will be using this formula every week on new data and want to write
something
that I can simply refresh.

Julie

"Ashish Mathur" wrote:

Hi,

You may create a pivot table. Drag Name to the row area and 401k, FICA
to
the data area. The pivot will auto summarise your data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet
and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column.
If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate
the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Ashish,
Thank you very much. I did not know that I could do that with Pivot tables.

I have yet another question.

So say instead of summing up the contents in the column that fits both name
and last name (Patrick Smith) criteria how can I have it sum the number of
instances in the table? Would I use a count if statement?

Thanks for your help.

"Ashish Mathur" wrote:

Hi,

You would not have to recreate the pivot everytime you add date by rows to
the existing database. Simply select the range (including the header row)
and convert it to a List(Excel 2003)/Table(Excel 2007). A table make the
database auto expanding. Now when you add data by rows, jut right click
on the pivot and Refresh (this will consider the new rows added).

If you anyways wish to work with formulas, then I think you have got the
desired solution from others.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
Ashish,

I don't want to use a pivot table because I was told I would have to
recreate the pivot table every time I wanted to update the spreadsheet. I
will be using this formula every week on new data and want to write
something
that I can simply refresh.

Julie

"Ashish Mathur" wrote:

Hi,

You may create a pivot table. Drag Name to the row area and 401k, FICA
to
the data area. The pivot will auto summarise your data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet
and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column.
If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate
the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Hi,

I am not clear about the question

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
Ashish,
Thank you very much. I did not know that I could do that with Pivot
tables.

I have yet another question.

So say instead of summing up the contents in the column that fits both
name
and last name (Patrick Smith) criteria how can I have it sum the number of
instances in the table? Would I use a count if statement?

Thanks for your help.

"Ashish Mathur" wrote:

Hi,

You would not have to recreate the pivot everytime you add date by rows
to
the existing database. Simply select the range (including the header
row)
and convert it to a List(Excel 2003)/Table(Excel 2007). A table make the
database auto expanding. Now when you add data by rows, jut right
click
on the pivot and Refresh (this will consider the new rows added).

If you anyways wish to work with formulas, then I think you have got the
desired solution from others.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
Ashish,

I don't want to use a pivot table because I was told I would have to
recreate the pivot table every time I wanted to update the spreadsheet.
I
will be using this formula every week on new data and want to write
something
that I can simply refresh.

Julie

"Ashish Mathur" wrote:

Hi,

You may create a pivot table. Drag Name to the row area and 401k,
FICA
to
the data area. The pivot will auto summarise your data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name.
For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet
and
find all entries labed Patrick and return the columns already
summed.
Similar
to a vlookup but able to know that if I want the 401k column it
should
return
400 for Patrick after finding his name twice and summing that
column.
If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate
the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.

Try this.

=SUM(IF(A3:A8="Julie",IF(B3:B8="Smith",1,0),0))

HTH
Regards,
Howard

"Julie" wrote in message
...
I have a worksheet that has multiple rows that have the same name. For
example.

I have a worksheet that looks like this
Name 401k FICA
Julie 100 200
Patrick 200 400
Sam 50 100
Julie 100 200
Julie 100 200
Patrick 200 400

I am trying to figure out how I can have excel look at the worksheet and
find all entries labed Patrick and return the columns already summed.
Similar
to a vlookup but able to know that if I want the 401k column it should
return
400 for Patrick after finding his name twice and summing that column. If
I
wanted the FICA column it would return 800 for that column.

I want to create a new tab where I can write a formula and aggregate the
data the way we want to view it for analysis. Is this possible?

I am using Excel 2003.