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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count the frequency of a specific number on a specific day |
| How to count non blanks 5 years from the last non blank date |
| How to count worksheets |
| How to count times between 2 specified times |
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.