Trying to count based off of dates and ticket status

I am trying to count the number of tickets that fit a few different types of status, which were created within a specific date range.

Ticket 1
01/14/2 07:31 AM

Ticket 2
01/07/21 10:25 AM

Ticket 3
12/22/21 03:27 PM

=COUNTIFS(L70:L433, {"Open","In Progress","Reopened","Awaiting CAB Approval","Support Team Triage"},K70:K426,">="&DATE(2021,1,1),K70:K426,"<="&DATE(2021,3,31))

I would expect this to return 1 but instead I am getting Err: 502. The help guide, and this says “Function argument is not valid. For example, a negative number for the SQRT() function, for this please use IMSQRT().”

It appears that the complaint is that I am using the DATE function, while trying to search for text. How do I format this correctly?

I have been looking online here, and I found some suggestions online here which seem to point towards using a mixture of commas and semicolons, or maybe using the SUMPRODUCT function? Is this where I should be looking?

The date cells, are formated as (and its content are) text or a date? You can see it pressing Ctrl+F8: dates are blue, text are black.

1 Like

The date cells, K70:K426, are shown as blue text.

I meant “search for text” as in the various statuses; Open, In Progress, etc. The statuses are shown as black text.

Yes that’s right:

For your sample data, I get 1 with formula
=COUNTIFS(L70:L433;{"Open","In Progress","Reopened","Awaiting CAB Approval","Support Team Triage"};K70:K433;">="&DATE(2021;1;1);K70:K433;"<"&DATE(2022;1;1))

BTW, do not use the expression <=DATE for the upper limit, it is better to use <NEXT_DAY


You seem to expect this to match any cell in L70:L433 that matches any of the values in inline array? No it will not; the argument is documented to be a criterion; and that doesn’t include arrays. You may use regular expressions like "Open|In Progress|Reopened|Awaiting CAB Approval|Support Team Triage" if you have regexes enabled.

1 Like

To elaborate a bit, to prevent possible confusion:

  1. The reason why you should not use "<="&DATE() is because any datetime for that day and some time will be greater than that value, and will not match: 2021-01-26 07:50 will not match "<="&DATE(2021;01;26), because 07:50 is greater than 00:00.
  2. The NEXT_DAY in @JohnSUN’s advise is not an existing function, it’s a value that you provide for the next day after your last wanted included date. It may be provided directly, or calculated as "<"&(DATE(...)+1).

Or will it? I see it working actually, even though this looks inconsistent with the function specification and definition of Criterion. @erAck: could you please provide some insight? Should it be fixed, or should ODF be fixed instead?

I added two more values to the test set - “Reopened” and “Awaiting CAB Approval”. The result of COUNTIFS() is {1;0;1;1;0}. Accordingly, SUM(COUNTIFS(...)) gives the correct result 3.

Could be so easy:

I didn’t check against actual code flow for the specific case, but in general an inline array argument forces the expression into array mode if the function does not accept/handle an array for that parameter (i.e. expects a scalar value); that might indeed work here as it evaluates the expression for each array element.

1 Like

But an array-mode evaluation of COUNTIFS should result in {1,0,1,…} - it shouldn’t be summed automatically, right? @JohnSUN mentioned SUM(COUNTIFS), but that’s not simple COUNTIFS; and the sum would likely not give a proper result - it may add partial matches, giving greater-than-correct number…

1 Like

No, COUNTIFS() doesn’t sum the individual counts, it returns an array. If not entered as array formula it displays the first result element. e.g. =COUNTIFS({1;2;3;2;1;2};{1;2}) displays 2 of the result {2;3}.

1 Like

You mean the case with the disabled option Search criteria = and <> must apply to whole cells?

Yes, that; or simply duplicates in the inline array… maybe there are more ways to shoot oneself in foot :slight_smile:

I see where my confusion arose. I saw exactly the case that you mentioned. I used “Reopened” as test case; and it matched both “Reopened” and “Open” (being case-insentitive, and without matching whole cells), giving me 2 for first match … which I confused for correct result.

So again: this function is not meant to be used with inline arrays, unless you really know what you are doing; use regular expression as criterion.

Yes, I agree with you. Perhaps such a workaround would be useful - to put all these values into an auxiliary range and use the address of the range (or each individual cell?) as the second parameter of COUNTIFS().

Thank you for the help! The formula provided was great! I did end up having to wrap it in a =SUM function, but once I did that, and modified it for the dates I need it for, it worked!

Noted on the < being better than <=. Would you say that the same rule also applies to >, instead of >=?

Thank you kindly for the reply. I found another suggestion that fit my needs, but I will investigate this for other spreadsheets I am working on.

No, for the lower limit >= is better. Otherwise, all “clean” dates will be excluded from the calculation. Judge for yourself, 2022-01-27 actually means 2022-01-27 00:00:00. If you use the > comparison, then 2022-01-27 00:00:01 and beyond will be included in the calculation, and the “clean” date will be skipped.