Apr 16 2021 12:24 PM
Hi,
Our company just switched the platform from Google to MS so I've been struggling to convert certain formulas to Excel, which I haven't used for many years now.
So here's the problem:
I have two tabs - the first one contains a sprint number, the second one a Start Date, and the third one an End Date for the sprint.
The other tab has a table with many columns where column T contains a date.
My goal is to count the number of tickets (or rows) from the table where column T is between the Start and End Dates from the first tab.
Note: each sprint has its own table on a separate tab, hence why I'm using INDIRECT to compose the name of the tab I'm looking for.
I tried different approaches using COUNTIF, COUNTA, FILTER, and PRODSUM but I always get some kind of error that I was able to find the root cause for.
Please help - sample spreadsheet is attached.
Thank you!
Apr 16 2021 12:51 PM
The criteria_range arguments for COUNTIFS cannot be expressions. They can only be ranges.
In D3:
=COUNTIFS(INDIRECT("'JQL - Sprint "& A3&"'!B:B"),"Bug",INDIRECT("'JQL - Sprint "&A3&"'!U:U"), "",INDIRECT("'JQL - Sprint "&A3&"'!T:T"),">"&B3,INDIRECT("'JQL - Sprint "&A3&"'!T:T"),"<"&C3)
Apr 16 2021 01:18 PM
SolutionIn the SUMPRODUCT formula, don't use entire columns, but finite ranges instead:
=SUMPRODUCT((INDIRECT("'JQL - Sprint "&A3&"'!B2:B100")="Bug")*(INDIRECT("'JQL - Sprint "&A3&"'!U2:U100")="")*(INDIRECT("'JQL - Sprint "&A3&"'!T2:T100")>B3)*(INDIRECT("'JQL - Sprint "&A3&"'!T2:T100")<C3))
Apr 16 2021 03:42 PM
You have made your move to MS at an interesting time. Excel development is now being influenced by academics of international reputation in the area of functional programming. Whereas Excel had started to lag Sheets, there are now features that make the application behave more like a software development platform than something for 'end users in denial of the fact that they are programming'. For example, for me, formulas normally begin
= LET(
and
= LAMBDA(x,y,
is beginning to creep in.
= LET(
type, INDIRECT("Sprint"&Sprint&"[Issue Type]"),
component, INDIRECT("Sprint"&Sprint&"[Components]"),
submit, INDIRECT("Sprint"&Sprint&"[SubmitDate]"),
bugCount, COUNTIFS(type,"Bug",component,"",submit,">="&StartDate,submit,"<="&EndDate),
bugCount)
Pretty much all calculation is array formula and anything really data-heavy would be Power Query. I will know I have passed the point of no return when I hear myself say "Oh, I nearly forgot, Excel will also run those legacy spreadsheet things!"
Caveat. These are just my opinions; as yet, far from mainstream.
Apr 16 2021 01:18 PM
SolutionIn the SUMPRODUCT formula, don't use entire columns, but finite ranges instead:
=SUMPRODUCT((INDIRECT("'JQL - Sprint "&A3&"'!B2:B100")="Bug")*(INDIRECT("'JQL - Sprint "&A3&"'!U2:U100")="")*(INDIRECT("'JQL - Sprint "&A3&"'!T2:T100")>B3)*(INDIRECT("'JQL - Sprint "&A3&"'!T2:T100")<C3))