Advice on sumif - more than one criteria
 

Hi there,

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

I have two worksheets. Worksheet 1 is my results page. Column A is for
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 of
hours and C whether the person is a '1', '2','3' or '4' as on the
results page. There may be many rows for each date and rows can repeat
themselves.

What I would like to do is have a formula to populate the results page
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 IF
Person is a '1'

For C2 -

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

etc.

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

Thank you...hope this makes some sense!

Robert


--
systematic
------------------------------------------------------------------------
systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294
View this thread: http://www.excelforum.com/showthread...hreadid=565941

Advice on sumif - more than one criteria
 

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


"systematic" wrote in message
...

Hi there,

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

I have two worksheets. Worksheet 1 is my results page. Column A is for
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 of
hours and C whether the person is a '1', '2','3' or '4' as on the
results page. There may be many rows for each date and rows can repeat
themselves.

What I would like to do is have a formula to populate the results page
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 IF
Person is a '1'

For C2 -

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

etc.

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

Thank you...hope this makes some sense!

Robert


--
systematic
------------------------------------------------------------------------
systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294
View this thread: http://www.excelforum.com/showthread...hreadid=565941