CODEWITHSUNDEEP
excelexcel-formulacountif
user3517375
user3517375

Reputation: 91

Excel count cells where difference between dates is between a range

I have a spreadsheet tracking issues and have various columns related to each issue, including the date the issue was discovered (column C), the date the issue actually started (column P), the date the investigation was started (column L), and the date the investigation ended and the issue was resolved (column M). I want to track how long an investigation has been open, how long it takes to complete an investigation, the time the issue started till it was discovered, and how long an issue was open; for each aspect I want to track, I'm grouping the counts into ranges (i.e., issues open 30 days or less, issues open between 31 and 60 days, etc.).

I was able to get the count of open issues using this formula (this one is for the 31 to 60 day grouping): =COUNTIFS($M:$M,"",$L:$L,"<"&TODAY()-31,$L:$L,">="&TODAY()-60)

However, I haven't been able to figure out the formula for the other things I want to track. Here's one formula I tried for tracking completion time: =COUNTIFS($M:$M,"*",$L:$L,"<"&$M:$M-31,$L:$L,">="&$M:$M-60). This formula returns 0 and when looking at the Function Arguments window, I get a #VALUE! error for Criteria2 ("<"&$M:$M-31) and Criteria3 (">="&$M:$M-60).

I'm guessing the issue is the date I'm trying to use in the second formula is not a constant like TODAY() is in the first formula but my Google skills haven't been sufficient to find an answer. Any ideas?

Upvotes: 0

Views: 195

Answers (1)

basic
basic

Reputation: 11968

You're right about criterias, COUNTIFS can't handle the criteria specified as an range. The SUMPRODUCT function should be used instead. Without seeing a sample, it is difficult to say exactly, but the problematic formula could be modified as follows:

=SUMPRODUCT(($L2:$L1000<$M2:$M1000-31)*($L2:$L1000>=$M2:$M1000-60))

Use the exact range, not the entire column. You can include empty cells, but not text (eg column names) or other non numeric data that may interfere with the calculation M-31 or M-60

Upvotes: 1

Related Questions