Sum a column of results from a VLOOKUP

[Legacy 193062]

How can a I sum a column of results that came from a VLOOKUP?

I have a column of cells that has this formula in it:
=IF(VLOOKUP($B$4&$A9&R$8,'Sheet1'!$J$2:$T$9998,11,FALSE)="OFF", "OFF", IF(VLOOKUP($B$4&$A9&R$8,'Daily Input'!$J$2:$T$9998,11,FALSE)="BR", "OFF", IF(VLOOKUP($B$4&$A9&R$8,'Sheet1'!$J$2:$T$9998,11,FALSE)="LU", "OFF", VLOOKUP($B$4&$A9&R$8,Table1[[Date and time]:[Compliance?]],12,FALSE))))

I get results like OFF and numerical values like 0, 1, 2, 3, and 4 from the formula above.

I want to sum the column to add up the numerical values but when I sum the column with the formula SUM, the result is 0.

I know I can copy and paste special values to get rid of the formulas and yes that works but I need to keep the formulas in place.

Any help would be appreciated.

 

[barry houdini]

You could shorten that a little using OR and then use +0 to convert to numeric - I'm assuming that apart from "OFF", "BR", and "LU" all other values returned by the VLOOKUP are numeric?

=IF(OR(VLOOKUP($B$4&$A9&R$8,'Sheet1'!$J$2:$T$9998,11,FALSE)={"OFF","BR","LU"}),"OFF",VLOOKUP($B$4&$A9&R$8,Table1[[Date and time]:[Compliance?]],12,FALSE)+0)

Now SUM should work....

 

[Jonmo1]

Welcome to the board...

Sum definately does sum numbers resulting froma formula.

If Sum is returning 0, and you can see there are numbers resulting from the vlookup..
Then they are not really numbers, they are "numbers stored as text"

in the 12th column of Table1, those values are likely not really numbers.
use =ISNUMBER(Cell)
Where Cell is one of the numbers in the 12th column of Table1.

Does it return True or False?

 

[Legacy 193062]

Thanks. Barry - your option worked.