multiple V-lookup

[Julie]

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.

 

[Pierre]

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

 

[L. Howard Kittle]

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

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

 

[L. Howard Kittle]

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]

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!

 

[L. Howard Kittle]

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 D510, 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(D510=G1,IF(E5:E10=G2,INDIRECT(H1,0),0)))

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

HTH
Regards,
Howard

 

[Ashish Mathur]

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]

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]

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]

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]

Hi,

I am not clear about the question

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Julie]

Sorry here is my question:

I want to count the number times "Julie Smith" appears in the table so that
it returns a value of 2. It seems easy but I can't figure out how do it it.

Thanks for your help.

 

[L. Howard Kittle]

Try this.

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

HTH
Regards,
Howard

 

[Ashish Mathur]

Hi,

Try this

=sumproduct((A2:A7="Julie")*(B2:B7="Smith"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Julie]

Ashish,

Thank you so much! that worked exactly how I needed. Are you able to explain
to me why you use the "*" sign?

Julie

 

[Ashish Mathur]

Hi,

The multiplication with the SUMPRODUCT() function implies an AND condition.
Plus is an OR condition

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com