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

HansVogelaar's avatar
HansVogelaar
MVP
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))

Resources