Help - How to count occurrences in a range that returns true or false

[ragomes1972]

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!

[#2]

@ragomes1972

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)

[#3 · Accepted solution]

@ragomes1972 

In 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))

[#4]

@ragomes1972 

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.