CODEWITHSUNDEEP
excel-formulaexcel-2010
Lien0
Lien0

Reputation: 95

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

Upvotes: 0

Views: 39

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27458

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

Upvotes: 1

Related Questions