countifs and a date that falls between two dates

kgomotso

Please help! I have two formulas that I am struggling with.

1. I am trying to count how many tasks have been completed/closed within the past fortnight. I came up with the following formula but it gives me the 'invalid operation' error. What am I doing wrong? Formula=COUNTIFS({LV_ BLD CONST QUERY REGISTER _status}, "closed", {LV_ BLD CONST QUERY REGISTER _ closed date}, >=TODAY(-14), {LV_ BLD CONST QUERY REGISTER _ closed date} <= TODAY())
2. I am trying to count the number of open queries that have been assigned to certain team members either as 'Resp' or 'Co-responsible' or both (meaning team member 1 = resp & team member 2 = co-resp or vice-versa) however I am getting the 'unparseable' error. Again, kindly advise what I may be doing wrong? Formula =COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _resp}, ("T_Mem1"; "T_Mem2")) + COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _co-resp}, ("T_Mem1"; "T_Mem2"))

All help will be appreciated , thanks

John C Murray

The first formula is missing a comma before "<=TODAY())"

John C Murray

Sorry to re-post, but the forum won't let me edit the previous post

The second formula is syntactically incorrect. The criterion parameters can only specify a single value. Just repeat the addition as you already started to do:

=COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _resp}, "T_Mem1")+ COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _resp}, "T_Mem2") + COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _co-resp}, "T_Mem1") + COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _co-resp}, "T_Mem2")

kgomotso

Thank you so much @John C Murray for sharing your knowledge. Both worked with no errors. Now I have a new issue with the second formula. If 2 or more team members are assigned onto one query as 'responsible' and 'co-responsible', it counts that as (for example) 3 queries if there are 3 names assigned to that instead of it just being one query. Arer you able to advise how I can work around that?

John C Murray

I'm not sure if this is what you are after, but you could set up the list of team member names in a column named "T_Mem".

Then in another column named (say) "Queries", set up your original query to reference the adjacent "T_Mem" cell on each row:

=COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _resp}, [T_Mem]@row )+ COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _co-resp}, [T_Mem]@row)

This will return the total number of queries assigned to each team member, regardless of whether they are responsible or co-responsible.

You could also create two other columns named "Resp" and "CoResp" if you need to get those counts separately for each team member.

"Resp" would contain the formula =COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _resp}, [T_Mem]@row )

and "CoResp" would contain =COUNTIFS({LV_ BLD CONST QUERY REGISTER _open status}, "open", {LV_ BLD CONST QUERY REGISTER _co-resp}, [T_Mem]@row)

kgomotso

Hi @John C Murray

The first suggestion only considers a query if all team members in the team member column have been assigned as either responsible or all as co-responsible. If for example a team has 3 members and less than all 3 members are either assigned as responsible or co-resp it returns a '0'. in this case my team members were placed in a single cell.

The second suggestion is not how I want the stats communicated. I just want each query to count as a single query against a team regardless of how many members of the team have been assigned as either responsible or co-responsible (in different combinations)

John C Murray

Sorry, but I have no idea what you are trying to achieve or what your data looks like

kgomotso

Hello @John C Murray

Below is a screenshot the LV_ BLD CONS QUERY REGISTER where there is one open query that has 2 of the 3 team members assigned to it. The two formulas below are referencing this register.

Below is a screenshot of the metrics sheet: my formula and the 3 team members. As can be seen below, the formula then reflects that there are 2 queries open instead of 1, on the basis that it has counted each instance of a team member being assigned as resp and co-resp

I have also tried placing the 3 team members in one cell. What happens then is that the formula '0' even when there is one query open unless if the query has all team members assigned as either resp or co-resp.

I hope you have a clearer picture of my data now

John C Murray

Thanks for posting that. I will have a look at it shortly