I have a table below
However, because this file is "link" to an external workbook by using a simple such as =[External WB]'Sheet1'!A1, there's some data that are actually not number, but text. I'd assume the creator uses "" as blank, instead of a numeric 0.
Given the SUMPRODUCT formula below, it return me a #VALUE! error.
My question now is that, what can I do on my SUMPRODUCT to convert all those #VALUE! to be a number, so that I can get the correct result.
Region |
Product |
Jan-18 |
... ... |
Dec-18 |
North |
A |
10 |
... ... |
20 |
South |
A |
|
... ... |
5 |
North |
B |
8 |
... ... |
|
North |
C |
7 |
... ... |
#N/A |
Edit: Was able to solve using IF(NOT(ISERROR())) function within SUMPRODUCT array formula.
Bookmarks