In range E5:E13, assume the entries are
1
2
#DIV/0!
4
5
6
#N/A
8
9
In cell I9, one may want to sum up the figures from cells E5,E7,E11 and E13. If one supplies the individual cell references to the SUM function, then the result will be a #DIV/0! error because the SUM function is intolerant towards errors supplied in its range. The formula which will resolve this problem is:
=SUMPRODUCT(SUMIF(INDIRECT(A11:A14),”>-1E100″))
A11:A14 has E5,E7,E11 and E13.
Thank you so much, this worked perfectly!
You are welcome. Thank you for the feedback.
i am trying to copy vlookup horizontally but it is not happening . can u please please help me out on this. also i need to know after defining a table can we still do naming for columns. if yes then will it be dynamic ?
please see the attached file.
THIS THE the formula i have used =VLOOKUP(N12, marks,2,0)
Hi,
Upload the workbook to OneDrive and share the link of the workbook here.
Hi Ashish
How about this: =SUM(IF(ISERROR(CHOOSE({1,2,3,4},E5,E7,E11,E13)),0,CHOOSE({1,2,3,4},E5,E7,E11,E13)))
entered as an array formula?
That way I don’t need to create a new set of contiguous data to indirect reference.
Let me know what you think
Patrick
Hi,
Thank you for sharing this. It works great. Since INDIRECT() and Array formulas are volatile, they would recalculate for the slightest change made anywhere in the workbook.
Ignore errors while adding non contiguous cells of a range
{ 7 Comments }