OzGrid

How to use Index/Countif based on multiple factors including range

Category: [Excel]  Demo Available

How to use Index/Countif based on multiple factors including range

Requirement:

The user has a vacation calendar that ranges from Jan-1-17 thru basically 2079 in a workbook.

The user is looking to calculate the amount of vacation days taken per person during a particular year.

If 2018 is selected from a drop down, basically it will count the number of instances "V" is identified between two dates (Jan1-18~Dec31-18), for that particular individual.

Solution:

=COUNTIFS(INDEX(Data!\$K\$3:\$CSW\$6,MATCH(K9,Data!\$J\$3:\$J\$6,0),0),"v",Data!\$K\$1:\$CSW\$1,">="&DATE(\$A\$1,1,1),Data!\$K\$1:\$CSW\$1,"<="&DATE(\$A\$1,12,31))

Obtained from the OzGrid Help Forum.

Solution provided by NBVC.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)