I have rows of cells that utilize INDEX & MATCH to return a NUMBER from a table.
The problem is, the users table has many cells which are blank, which returns ZERO. If the result is zero, the user needs to go back to the table and update it so a correct number is returned. My solution to this was if the result is Zero, return "db err"
=IF(INDEX('Table'!$A$1:$AJ$103,MATCH(EA$3,'Table'!$A$1:$A$103,0),MATCH($J6,Table'!$A$1:$AJ$1,0))=0,"db err", INDEX('Table'!$A$1:$AJ$103,MATCH(EA$3,'Table'!$A$1:$A$103,0),MATCH($J6,'Table'!$A$1:$AJ$1,0))*EA6)
I have a Named Range OperCategHdr =Sheet1'!$CX$4:$FF$4, which is the header row. Each cell in this header row might contain, ABC, DEF, XYZ, etc
I have a formula to sum all the cells in a given row where the Header Column is "X"
For example:
=SUMIF(OperCategHdr,"ABC",CX6:FF6)
=SUMIF(OperCategHdr,"XYZ",CX6:FF6)
I thought that by returning "db err" instead of a number when the cell is blank, it would result in an error in the SUMIF, but it doesn't, it simply sums all the numbers and ignores the text.
I need a method of NOT returning an answer to the SUMIF when there is missing data AND a method of notifying the user which data is missing ["db err"].
TO combat the SUMIF, I could use =IF(COUNTIF(CX6:FF6,"db err") >0,"error", SUMIF(OperCategHdr,"ABC",CX6:FF6)) but that's just more calculations in an already huge workbook.
I tried =SUMPRODUCT(--(OperCategHdr="MIN/"),CX6:FF6) but that ignores the text also.
Bookmarks