Advice on sumif - more than one criteria

[systematic]

Hi there,

I was hoping someone could give me some guidance with a formula I nee
to work out.

I have two worksheets. Worksheet 1 is my results page. Column A is fo
the date and columns B-E are labelled 1,2,3,4.

Worksheet 2 is the data. Column A contains a data, B the number o
hours and C whether the person is a '1', '2','3' or '4' as on th
results page. There may be many rows for each date and rows can repea
themselves.

What I would like to do is have a formula to populate the results pag
which would be in B2, C2, D2, E2 then filled down for each date.

eg. In english

For B2 -

If Worksheet1 Date = Worksheet2 Date SUM Worksheet2 Number of Hours I
Person is a '1'

For C2 -

If Worksheet1 Date = Worksheet2 Date SUM Worksheet2 Number of Hours I
Person is a '2'

etc.

I can use SUMIF(Worksheet1!A:A, Worksheet2!A2, Worksheet2B:B) to coun
the total hours for a day...but not sure how to add the second criteri
to split the different types of people (1,2,3,4).

Thank you...hope this makes some sense!

Rober

 

[Bernie Deitrick]

Robert,

Forget formulas. Use a Pivot Table on your worksheet2 data table, with Column A as the row field,
Column C as the column field, and Column B as the data field, set to sum.

HTH,
Bernie
MS Excel MVP