This formula results with #VALUE! error as some columns and/or rows are blank. Is there a way to get a result while keep blanks in the data sheet?

=SUMPRODUCT(Data!$E$2:$JN$200*(Data!$A$2:$A$200=$B8)*(Data!$E$1:$JN$1=M$3))

CodePudding user response：

Per screenshot - I have plenty of blanks yet your equation is working perfectly...

HOWEVER, if I place text in the sumproduct range (viz: E2:JN200) then I get an error which I can circumvent as follows:

=SUMPRODUCT(IFERROR(Data!$E$2:$JN$200*(Data!$A$2:$A$200=$B8)*(Data!$E$1:$JN$1=M$3),0))