Looking up values with multiple criteria

  • Thread starter Thread starter CodyT
  • Start date Start date
C

CodyT

I have 3 columns(department, date and wage hours) the department and the
dates repeat so that it states:

Maintenance 7/5/08 41
Warehousing 7/5/08 37
Maintenance 7/10/08 48
Warehousing 7/10/08 28

At the beginning of each new date it starts the departments over again. I
need to organize the info so that there the dates and departments are not
repeating. The end product needs the dates across the top row and the four
different departments on the left column with the wages fulling the data
area.

Please help.
 
Suppose your data is on Sheet1, occupying A1:C100. Insert a new sheet
and put your dates in row 1 starting with B1 and put your 4
departments in column A starting with A2, so that you have something
like this:

7/5/08 7/10/08 and so on ...
Maintenance
Warehousing
etc ...

Then in B2 you can put this formula:

=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)*(Sheet1!
$C$1:$C$100))

(adjust the ranges to suit your data). Copy the formula down into
B3:B5, then copy B2:B5 across for as many dates as you have.

Hope this helps.

Pete
 
Back
Top