IF Count Formula Issue

Question

I have issue with following formula it will not allow formula to work correctly.

=SUM(Data!V4,C22+C23+C24+C25+C26) it gets its cell data from =IFERROR(VLOOKUP(AF25,AF26:AG26,2,FALSE),"")

=IF COUNT does not work it when the cell is empty but show actual "0" in the cell.

 This example formula is from another portion of the sheet that works fine!

=IF(COUNT(Data!AB4,C36,C37,C38)=4,SUM(Data!AB4,C36,C37,C38),"Missing Data For Total!")

I know this sounds little vague but maybe you can ask questions so I can fill in the blanks.

Thanks

Answer 1

@Leon

Where does this wisdom come from?

"However, this formula will not work because AF26:AG26 is not a valid table_array. It only has one row and two columns. You need to have at least two rows and two columns for VLOOKUP to work."

VLOOKUP will work just fine on a 1 row X 2 column range/array. It will even work when the lookup array is only a single cell, though it would be meaningless. but it works!

@Scott5932

When you separate the arguments in a SUM formula with comma's, Excel will ignore cells that contain texts. However, using the plus signs will raise a #VALUE! error if any of the cells contains text.

Since you mention that the cells contents come from a VLOOKUP formula that may return "" (i.e. an empty text string) if no match is found, any of the cells C22 ,C23, C24, C25 or C26 may contain an empty string. If so, that will raise an error.

Now, I have no idea about what you are trying to achieve, but you could re-write the first formula you mention to this:

=SUM(Data!V4,C22:C26)

No need to mention every single cell from C22 to C26 if you want to include all cells in the SUM formula.

Answer 2

Hello, I am Leonielhou, an Independent Advisor and a user like you, I am happy to help clarify any questions you may have.

Based on your description, you are trying to use VLOOKUP to look up a value in a table and then sum it with other values in another sheet? However, your formula is not working correctly because you are using the wrong syntax for VLOOKUP.

the syntax for VLOOKUP is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

where lookup_value is the value you want to find in the first column of the table_array, table_array is the range of cells that contains the data, col_index_num is the column number in the table_array that has the value you want to return, and range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE). In your formula, =SUM(Data! V4,C22+C23+C24+C25+C26) it gets its cell data from =IFERROR(VLOOKUP(AF25,AF26:AG26,2,FALSE),“”) you are using AF25 as the lookup_value, AF26:AG26 as the table_array, 2 as the col_index_num and FALSE as the range_lookup. However, this formula will not work because AF26:AG26 is not a valid table_array. It only has one row and two columns. You need to have at least two rows and two columns for VLOOKUP to work. To fix this issue, you need to expand your table_array to include more rows and columns that contain your data. For example, =SUM(Data! V4,C22+C23+C24+C25+C26) it gets its cell data from =IFERROR(VLOOKUP(AF25,A1:B10 ,2,FALSE),“”) where A1:B10 is a sample table_array that has more than one row and column.

I hope this helps.