Forum Discussion

ragomes1972's avatar
ragomes1972
Copper Contributor
Apr 16, 2021
Solved

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

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...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 16, 2021

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 16, 2021

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.

 

Resources