Looking up values that are repeated in a column
 

I am trying lookup data in a table that is repeated numerous times in a
column. I want to get the total for that data. For example, Col A is
Proj Name, Col B is cost center, Col C is amount billed, Col D is Month
billed, and Col E is employee. I want to be able to look up all that
was billed to Project A for Cost Center 1234 in January...etc. etc...


--
KC2006
------------------------------------------------------------------------
KC2006's Profile: http://www.excelforum.com/member.php...o&userid=35391
View this thread: http://www.excelforum.com/showthread...hreadid=551661

Looking up values that are repeated in a column
 

Hi!

Use the Autofilter.

Select a cell in your data range
Goto DataFilterAutoFilter

From each drop down select the criteria to filter that column on.

It's real easy!

This could also be done with formulas but it's a whole lot more complicated.

Biff

"KC2006" wrote in
message ...

I am trying lookup data in a table that is repeated numerous times in a
column. I want to get the total for that data. For example, Col A is
Proj Name, Col B is cost center, Col C is amount billed, Col D is Month
billed, and Col E is employee. I want to be able to look up all that
was billed to Project A for Cost Center 1234 in January...etc. etc...


--
KC2006
------------------------------------------------------------------------
KC2006's Profile:
http://www.excelforum.com/member.php...o&userid=35391
View this thread: http://www.excelforum.com/showthread...hreadid=551661

Looking up values that are repeated in a column
 

That works for me to see it on the current sheet. What if I have
another sheet I want the data returned to??? Sort of like doing a
VLOOKUP, but returning the sum of the lookups.


--
KC2006
------------------------------------------------------------------------
KC2006's Profile: http://www.excelforum.com/member.php...o&userid=35391
View this thread: http://www.excelforum.com/showthread...hreadid=551661

Looking up values that are repeated in a column
 

Like I said, it can be done with formulas but it's complicated.

How many rows of data are there? If there are 1000's of rows, using formulas
isn't very efficient.

Biff

"KC2006" wrote in
message ...

That works for me to see it on the current sheet. What if I have
another sheet I want the data returned to??? Sort of like doing a
VLOOKUP, but returning the sum of the lookups.


--
KC2006
------------------------------------------------------------------------
KC2006's Profile:
http://www.excelforum.com/member.php...o&userid=35391
View this thread: http://www.excelforum.com/showthread...hreadid=551661

Looking up values that are repeated in a column
 

Hold on there just a second!

I think I may have misunderstood what you want.

You just want the total sum of amounts?

Biff

"Biff" wrote in message
...
Like I said, it can be done with formulas but it's complicated.

How many rows of data are there? If there are 1000's of rows, using
formulas isn't very efficient.

Biff

"KC2006" wrote in
message ...

That works for me to see it on the current sheet. What if I have
another sheet I want the data returned to??? Sort of like doing a
VLOOKUP, but returning the sum of the lookups.


--
KC2006
------------------------------------------------------------------------
KC2006's Profile:
http://www.excelforum.com/member.php...o&userid=35391
View this thread:
http://www.excelforum.com/showthread...hreadid=551661

Looking up values that are repeated in a column
 

Yes, just the total. For example, if I have 10 employees (10 rows of
data) from cost center 1234 book their time to Project A I want the
total time booked to that project for each month.


--
KC2006
------------------------------------------------------------------------
KC2006's Profile: http://www.excelforum.com/member.php...o&userid=35391
View this thread: http://www.excelforum.com/showthread...hreadid=551661

Looking up values that are repeated in a column
 

Ok, that's easy!

A2:A20 = proj names
B2:B20 = cost center
C2:C20 amount billed
D2:D20 = month (as a TEXT entry: Jan, Feb, Mar, etc)
E2:E20 = employee

=SUMPRODUCT(--(A2:A20="some_proj_name"),--(B2:B20="some_cost_center"),--(D2:D20="some_month"),--(E2:E20="some_employee"),C2:C20)

It's better to use cells to hold the criteria:

G1 = proj name = 2007 upgrade
H1 = cost center = AA100
I1 = month = Mar
J1 = employee = Smith

=SUMPRODUCT(--(A2:A20=G1),--(B2:B20=H1),--(D2:D20=I1),--(E2:E20=J1),C2:C20)

Biff

"KC2006" wrote in
message ...

Yes, just the total. For example, if I have 10 employees (10 rows of
data) from cost center 1234 book their time to Project A I want the
total time booked to that project for each month.


--
KC2006
------------------------------------------------------------------------
KC2006's Profile:
http://www.excelforum.com/member.php...o&userid=35391
View this thread: http://www.excelforum.com/showthread...hreadid=551661