SumProduct VS Lookups

[jlmccabes]

Dept Acct Hrs
Have columns with 9160 3210 160
9160 3610 80

Worksheet with ALL depts and all accts listed on it.
Need to fill it in with just the data that matches dept , acct, hrs.
Sumproduct will work, little clumsy for me, is there any way to get Vlooup
to look at both coluns on both sheets and pull the hours numbers only when
they match up.

 

[Roger Govier]

Hi

One way
Insert a row above your header row and insert in C1
=SUBTOTAL(9,C3:C1000)
change range to suit.

Mark the Header row, Data>Filter>Autofilter
Use the dropdown on Department, then Acct and the Total Hours will show
in C1.

Better still, create a Pivot Table.
Mark the range of data, >Data>Pivot Table>Next>Finish
Drag Dept to the Page area
Drag Acct to the Row Area
Drag Hrs to the Data area

Use the filters to show whichever combinations you want.

For more help on Pivot tables take a look at the following sites

http://www.contextures.com/xlPivot02.html

look on Contextures site also for help in setting up Dynamic ranges to
deal with expanding sets of data.

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx