Countif + vlookup

[baker_89]

Hi 1st Time posting,

Sheet 1 has a table with 5 columns (Name (A1), Date (B1), Type of Occurrence (C1), Reason (D1), (Un)Excused (E1)), the fields are filled through questions through a user form. The contents will vary within a set number of values from the above mentioned.

On Sheet 2 I have 3 columns (Name (A3), Absent (B3), Tardy (C3)).

I want Sheet 2 to look up the Names (A) from Sheet 2 to the Names (A) on Sheet 1 and count the matching number of "Absent" (B) and "Tardy" (C) From the Type of Occurrence column on Sheet 1.

Just to show a total count of each time it comes up.

I've tried a few different formulas and sure I could use a pivot table to show this as well but I would like to try it using a formula.

 

[MrMeinHerr]

Hi 1st Time posting,

Sheet 1 has a table with 5 columns (Name (A1), Date (B1), Type of Occurrence (C1), Reason (D1), (Un)Excused (E1)), the fields are filled through questions through a user form. The contents will vary within a set number of values from the above mentioned.

On Sheet 2 I have 3 columns (Name (A3), Absent (B3), Tardy (C3)).

I want Sheet 2 to look up the Names (A) from Sheet 2 to the Names (A) on Sheet 1 and count the matching number of "Absent" (B) and "Tardy" (C) From the Type of Occurrence column on Sheet 1.

Just to show a total count of each time it comes up.

I've tried a few different formulas and sure I could use a pivot table to show this as well but I would like to try it using a formula.

If you are using Excel 2007 or later, you can try the following in cell B4 on Sheet2 and copy to column C and down:

=COUNTIFS(Sheet1!$A:$A,Sheet2!$A4,Sheet1!$C:$C,Sheet2!B$3)

If you are using an earlier version of Excel, you can use an array formula in cell B4 and copy to column C and down:

=SUM(IF((Sheet1!$A:$A=Sheet2!$A4)*(Sheet1!$C:$C=Sheet2!B$3),1,0))

Remember that the array formula must be entered with Ctrl+Shift+Enter and not just Enter.

I hope this helps!