Formula that looks at more than one criteria and then adds up those that match
I have a spreadsheet with two worksheets
Worksheet 1
for arguments sake assume that in
column A is the students name
column B the lesson (i.e Math, History, Science etc)
So John Smith could be in A1:A10 multiple times for multiple lessons
Worksheet 2
So assume the raw data is in this worksheet, and it has
Column A as Names
Column B is the lesson
column C has a number of hours of lessons attended
i.e (Worksheet 2)
A1 (John Smith) ...........B1 (History) ..........C1 (3)
A2 (John Smith) ...........B2 (Math) .............C2 (2)
A3 (Bill Jones) ..............B3 (History) ..........C3 (1)
A4 (John Smith)............B4 (History) ...........C4 (2)
(Worksheet 1)
A B C
A1 (John Smith) ...............B1 (History) ..............C1=????
A2 (John Smith) ...............B2 (Math) .................
I want to add a formula in Worksheet 1 in cell C1 that says look at Worksheet 2 and any time in Column A you see the name of the person shown in A1 of Worksheet 1 & you also see the contents of Worksheet 1 cell B1 then add the number of hours in Worksheet C1:C10
So for the example above it should return the value of 5
I thought I had it but am lost and very confused.
Is it a countifs formula Sumifs or something else.
I hope my description makes some sense
|