Excel exercise 1

find the number of unique employees who took vacations. If someone took multiple vacations, then count it as 1.

=AND([@[Start Date]]>=start,[@[End Date]]<=end)*(COUNTIFS($B$4:B4,B4,$C$4:C4,">="&start,$D$4:D4,"<="&end))

=COUNTIF(vacations[Dummy],1)

=and(start date 1>=condition cell 1,end date<=condition cell 2) checks to see if the dates in the start and end date range before and after the specified conditions. This returns true for each cell in range where the condition is met and false for cells that don’t. In this case, it evaluates cells in both ranges between February 1 and March 31.

(countifs($b$4:b4,b4,$c$4:c4,”>=”condition cell 1,$d$4:d4,”<=”condition cell 2) counts the number of rows where the employee name in cell b4 matches the employee name in b4, the date in c4 is greater than or equal to the February 1, and the date in d4 is less than or March 31. This counts the number of times each employee took a vacation between February 1 and March 31. * converts the true of false results from the and function into 1s and 0s and multiplies it with the numeric results of the countifs function.
countif(count column, 1) counts the number of values in the range created by the previous formula where the value is 1.

find the number of unique employees who took vacations. If someone took multiple vacations, then count it as 1.

=AND([@[Start Date]]>=start,[@[End Date]]<=end)*(COUNTIFS($B$4:B4,B4,$C$4:C4,">="&start,$D$4:D4,"<="&end))

In the formula above why is

=AND([@[Start Date]]>=start,[@[End Date]]<=end)

required instead of just using

=COUNTIFS($B$4:B4,B4,$C$4:C4,">="&start,$D$4:D4,"<="&end)

To return the number of unique users per who took vacations between February 1 and March 31 given that both evaluate for start and end dates before and after the specified dates.

The and function returns true for each cell in the selected ranges where both conditions are met and false for cells that don’t. In this case, true indicates that the value in the start date range is during and after February 1 and the end range is during and before March 31. The true or false values returned by the function are converted into 1s and 0s when multiplied by the results of the countifs function. The and function is necessary because the countifs function can’t verify whether the start and end date ranges fall within the specified range. And ensures that only cells from the start and end date ranges with dates between February 1 and March 31 are counted.

find the number of unique employees who took vacations. If someone took multiple vacations, then count it as 1.

=AND([@[Start Date]]>=start,[@[End Date]]<=end)*(COUNTIFS($B$4:B4,B4,$C$4:C4,">="&start,$D$4:D4,"<="&end))

Why does the formula above work but the formulas below don’t

=AND([@[Start Date]]>=”1-Feb-12”,[@[End Date]]<=”31-Mar-12”)*(COUNTIFS($B$4:B4,B4,$C$4:C4,">=1-Feb-12",$D$4:D4,"<=31-Mar-12"))

=AND([@[Start Date]]>=start,[@[End Date]]<=end)*(COUNTIFS($B$4:B4,B4,$C$4:C4,">="&start,$D$4:D4,"<="&end))

=AND([@[Start Date]]>=start,[@[End Date]]<=end)*(COUNTIFS($B$4:B4,B4,$C$4:C4,">=start",$D$4:D4,"<=end"))

The dates written in ““ are treated as strings, not dates, so Excel won’t properly perform a comparison. The & operator allows Excel to properly perform a comparison by combing the comparison within the ““ with the actual value in the selected cell. The date function can also resolve this issue.

Calculate the total number of vacations taken between February 1 and March 31.

=COUNTIFS(vacations[Start Date],">=1-Feb-12",vacations[End Date],"<=31-Mar-12")

=countifs(condition range 1, “>1-Feb-12”,condition range 2,”<=31-Mar-12”) counts the number of cells in each range where the condition is met and then adds them together. This returns the number of vacations taken between February 1 and March 31