excel-formulaexcel-2010

Excel Sumproduct to ignore non numerical value

Why is my formula not working? The result that i'm having is #VALUE!

I tried changing the (C5:513<>"-") to (Isnumber(C5:513)) but it still did not work. I am open for better formula on this.

Trying to limit the formula that can work on 2016 Ms Excel version or lower.

enter image description here

Solution

  • Here are two alternative ways using SUMPRODUCT()+IFERROR()/IF() which you could try using:

    The below formulas uses your logic as already stated in the screenshot, however when there is no date for end brackets it would return a 0 as for the 5th Jan 2025.

    • Option One:

    =SUMPRODUCT(
 IF($C$5:$K$13="-",0,$C$5:$K$13)*
   ($B$5:$B$13=$E17)*
   ($C$4:$K$4>=F$16)*
   ($C$4:$K$4<=G$16))

    • Or, Option Two:

    =SUMPRODUCT(IFERROR(
 ($C$4:$K$4>=F$16)*
 ($C$4:$K$4<=G$16)*
 ($E17=$B$5:$B$13)*
 ($C$5:$K$13),0))