• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sum 3 cells having array formulas ignoring #N/A error

Dear Ninjas.
Can anybody help me with this.
how to sum three cells having array formulas consisting of index, match, countifs functions while ignoring #N/A error in one of the cell.

Regards
A.Ali
 
Hi Ali,

Here are two alternatives:

Excel 2010+ (non-array formula:)
=AGGREGATE(9,6,E13:G13)

Any Version (Array formula:
=SUM(IF(ISNUMBER(E13:G13),E13:G13))
Confirm with Ctrl+Shift+Enter

Assuming formula range os E13:G13, change the range as per your need.

Regards,
 
AGGREGATE() is super if you have XL 2010 or later.

These will also work in any version and don't need CSE:

=SUM(SUMIF(E13:G13,{"<0",">0"}))

=SUMIF(E13:G13,"<="&9.99E307)
 
Thanks Somendra and Colin.
I have tried but its showing "0" in the cell.

in cell A1 i have used "=LEFT(INDEX([Physical_Data_source.xlsx]PG1!$B$21:$H$21,MATCH(1,COUNTIFS(A3,[Physical_Data_source.xlsx]PG1!$B$2:$H$2),0)),3)"

Result is 250 value

in cell B1 i have used "=LEFT(INDEX([Physical_Data_source.xlsx]PG1!$B$21:$H$21,MATCH(1,COUNTIFS(A3,[Physical_Data_source.xlsx]PG1!$B$2:$H$2,[Physical_Data_source.xlsx]PG1!$C$3,[Physical_Data_source.xlsx]PG1!$B$3:$H$3),0)),3)"

Result is 200 value

and in C1 cell i need sum of 250 and 200 which should be 450. but by using above formulas it shows 0 (used ctr+shift+enter)

Can you help me out please
 
@Asfandyar Ali

That's because your formula in A1 & B1 will return a number as text, Can you use -- before your LEFT function or add 0 at the end of LEFT function. Something like this:

=--LEFT(INDEX([Physical_Data_source.xlsx]PG1!$B$21:$H$21,MATCH(1,COUNTIFS(A3,[Physical_Data_source.xlsx]PG1!$B$2:$H$2),0)),3)

Or

=LEFT(INDEX([Physical_Data_source.xlsx]PG1!$B$21:$H$21,MATCH(1,COUNTIFS(A3,[Physical_Data_source.xlsx]PG1!$B$2:$H$2),0)),3)+0

Regards,
 
Last edited:
Or There can be one more way of handling this: Try below suggestion for both your formula:

=IFERROR(--LEFT(INDEX([Physical_Data_source.xlsx]PG1!$B$21:$H$21,MATCH(1,COUNTIFS(A3,[Physical_Data_source.xlsx]PG1!$B$2:$H$2),0)),3),0)

And simply use SUM than

Regards,
 
Back
Top