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.
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.
Share Improve this question edited Jan 20 at 19:03 Mayukh Bhattacharya 27.2k8 gold badges29 silver badges42 bronze badges asked Jan 20 at 18:49 Lien0Lien0 957 bronze badges 2 |1 Answer
Reset to default 1Here 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))
=SUMPRODUCT(IFERROR(($C$4:$K$4>=F$16)*($C$4:$K$4<=G$16)*($E17=$B$5:$B$13)*($C$5:$K$13),0))or=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))– Mayukh Bhattacharya Commented Jan 20 at 18:57