CODEWITHSUNDEEP
vbaexcelexcel-formula
Floppityflip
Floppityflip

Reputation: 35

CountIfs date range AND conditional, possibly a VBA solution

Okay, so here is a sample my current code. I have this in several cells , where B-H7 reflect the absence type it is searching for.

=COUNTIFS('Old Data'!$A:$A,Tracking!A8,'Old Data'!$B:$B,$E$7, 'Old Data'!D:D, ">"&$B$5, 'Old Data'!D:D, "<"&$C$5)

What this code is doing is looping through a spreadsheet, where the employee name is found in the data sheet, comparing the absence types and assorting them to their respective columns and counting them, the last bit of code restricts the search between date ranges.

That being said, I need to add conditions to this that I'm not sure I can without taking it into VBA. In the "Old Data" Sheet in column D I have start time, which displays in MM/DD/YY HH:MM format. In Column E I have End Time, which displays in the same MM/DD/YY HH:MM format.

I need to have a way to

A.) Have the progam count the number of days between these dates and a +1 to the count for each respective day.

B.) If the start and end date are the same, have the program compare the number hours. if it is less than 4, only add a .5 to the counter.

My first thought is to scratch the countifs formula and loop through and parse it out using VBA, but I thought I'd check first to see if it can be done with just the formula as the power of the built in function has been pretty surprising to me so far.

I think I should probably take this from a formula to a VBA function and call it in the cells, but I'm not entirely sure, pretty new to the VBA/Excel scene.

Also, I'm in Excel 2007.

Thanks for any input on this issue!

Upvotes: 0

Views: 1641

Answers (1)

barry houdini
barry houdini

Reputation: 46331

It's possible to do with a formula but not with COUNTIFS. This array formula should do it

=SUM(IF(('Old Data'!$A$2:$A$100=Tracking!A8)*('Old Data'!$B$2:$B$100=$E$7)*('Old Data'!$D$2:$D$100>$B$5)*('Old Data'!$D$2:$D$100<$C$5),IF('Old Data'!$E$2:$E$100-'Old Data'!$D$2:$D$100<"4:00"+0,0.5,INT('Old Data'!$E$2:$E$100)-INT('Old Data'!$D$2:$D$100)+1)))

confirmed with CTRL+SHIFT+ENTER

I restricted the data range to rows 2 to 100, adjust as required, whole columns is possible but that may slow down the formula considerably

To count workdays only change to this version:

=SUM(IF(('Old Data'!$A$2:$A$100=Tracking!A8)*('Old Data'!$B$2:$B$100=$E$7)*('Old Data'!$D$2:$D$100>$B$5)*('Old Data'!$D$2:$D$100<$C$5),IF('Old Data'!$E$2:$E$100-'Old Data'!$D$2:$D$100<"4:00"+0,0.5,NETWORKDAYS('Old Data'!$D$2:$D$100+0,'Old Data'!$E$2:$E$100+0))))

You can also exclude holidays if you add a holiday range to the NETWORKDAYS function

Upvotes: 1

Related Questions