trying to select a value based on 2 criteria defined in 2 drop lists

[falcon4]

here is a sample of the data i'll be working thru each month.
we have a project name, task number , and reported hours.
I'd like to make a formula that allows me to select the project name from a drop down, select the task, again from a drop down, and then have it report the associated hours.
i expect to create a bunch of rows that will have the lists, and formula. from there i can create various reporting charts.



Project Name task Hrs
1-6-05 & B19925-1 FAMILY 1.1 Eng Labor 1.5
1-6-05 & B19925-1 FAMILY 1.2 Mfg Labor 164.25
1-6-05 & B19925-1 FAMILY 1.3 Material 0
1-6-05 & B19925-1 FAMILY 1.4 Odc 0
1552 FAMILY 1.1 Eng Labor 0
1552 FAMILY 1.2 Mfg Labor 25.5
1552 FAMILY 1.3 Material 0
1552 FAMILY 1.4 Odc 0
2279 FAMILY 1.1 Eng Labor 10.5
2279 FAMILY 1.2 Mfg Labor 19
2279 FAMILY 1.3 Material 0
2279 FAMILY 1.4 Odc 0

I can do a vlookup, but i don't know how to combine the 2 serch criteria of project and task

Thanks for you assistance

Gary

 

[DPlank]

I'd look at sumproduct instead.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[AnotherHiggins]

SumProduct is totally the way to go.

quasi-formula example:
=SumProdct( --(Project Name = DropDown1) * --(task = Dropdown2) * --(Hrs))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

<cough>Pivot Table with 2 page fields - no need for formulae</cough>

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[falcon4]

I should clarify one item. While the table i provided shows 4 different task numbers which repeat, in reality there are many more.
I would like to return the hours based on selected project, and selected task.

I tried the sum product, but that made no sense to me and returned a #name? error.

the pivot table may work, but i am trying to build a chart based on returned values for various projects and tasks, and the table approach confuses me.

 

[xlbo]

More clarification = Pivot Table with Project & Task as ROW fields. Hours as DATA field. then you can just create a Pivot chart

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Pivot Table is a fine solution, but FWIW, here's an explanation of how SumProduct works.

You didn't just copy and paste my example, did you? You'll need to put in ranges or use named ranges. If you post the formula you tried, we can help you figure out why it didn't work.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[falcon4]

Here are the 2 formulas i tried to evolve from your example

=SumProdct(--(H16:H27=$N$14)*--(I16:I27=$O$14)*(J16:J27))


=SumProdct( --(PROJECT= N14) * --(TASK = O14) * --(HOURS))
where i have created names for the ranges.

i reviewed the sumproduct description you provided and found it very interesting. i am assuming using H16:H27=$N$14 where $N$14 is the selected value from the drop down list is acceptable.

 

[falcon4]

now these work

=SUMPRODUCT(--(PROJECT=N14),--(TASK=O14),(HOURS))

or

=SUMPRODUCT(--(PROJECT=N14)*--(TASK=O14)*--(HOURS))

i musta pissed off the first buncha formulas somehow

 

[falcon4]

i just realized i used the shortened formula "sumprodct" which obviously doesn't work

thanks for all the help and the education on this formula, this will work out well,

gary

 

[AnotherHiggins]

Dang typos. Sorry for the confusion caused by my leaving the second U out of sumproduct, but glad you got it sorted.

I would suggest looking into pivot tables when you get time. Very useful and dead simple once you get the hang of them.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[falcon4]

thanks again, I have and will continue to brush up on their advantages

Gary