Looking up values that are repeated in a column

[KC2006]

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

 

[Biff]

Hi!

Use the Autofilter.

Select a cell in your data range
Goto Data>Filter>AutoFilter

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]

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

 

[Biff]

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

 

[Biff]

Hold on there just a second!

I think I may have misunderstood what you want.

You just want the total sum of amounts?

Biff

 

[KC2006]

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.

 

[Biff]

Ok, that's easy!

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

=SUMPRODUCT(--(A2:A20="some_proj_name"),--(B2:B20="some_cost_center"),--(D220="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),--(D220=I1),--(E2:E20=J1),C2:C20)

Biff