Struggling with a countifs

Question

I have a table that is set as;

Name	Date1	Date 2	Date 3	etc
persons name	AL	AL	AL	AL

Where if it is AL or not determines if they are on annual leave

I'm trying to make a new table on a seperate sheet that will tell me how many times Person1 is on "AL" in the table on the other sheet

My table is set up on the other sheet as

Name	AL#
[Persons name	FORMULA?

I've tried Countifs(Sheet1!A:A,A1,Sheet1! (where A1 is the persons name on sheet 2)

And I'm just stuck

Answer 1

Your data is on one row in the source sheet. However, you need a columnar layout to implement COUNTIFS. There are other alternatives such as SUMPRODUCT which can be used in your case. See below demonstration.

Sheet 1 : B2:J20 houses date wise leave data that contains "AL". Column A2:A20 holds unique names.

Sheet 2: Cell A2 houses the name

Formula in such case would be:

=SUMPRODUCT((Sheet1!$B$2:$J$20="AL")*(Sheet1!$A$2:$A$20=Sheet2!A2))