Looking up values that are repeated in a column

[KC2006]

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

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[KC2006]

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.

[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

"KC2006" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>

[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
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>