Trying to count based off of dates and ticket status

English
, , , ,
#1

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.

Example:
Ticket 1
01/14/2 07:31 AM
Open

Ticket 2
01/07/21 10:25 AM
Done

Ticket 3
12/22/21 03:27 PM
Open

=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?

#3

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
#4

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.

#5

Yes that’s right:
HELP_COUTIFS

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

2 Likes
#6

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
#7

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).
2 Likes
#8

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?

#9

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.

#10

Could be so easy: https://ask.libreoffice.org/uploads/short-url/nW2PUGMTAXtJcBy67iiy8RvNj8m.odb

#11

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
#12

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
#13

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
#14

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

#15

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.

#16

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().
COUNTIFS

#17

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 >=?

#18

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.

#19

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.