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

[Asfandyar Ali]

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

 

[Somendra Misra]

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,

 

[Colin Legg]

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)

 

[Asfandyar Ali]

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

 

[Somendra Misra]

@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,

 

[Asfandyar Ali]

sorry did not get that. can you please mention your suggestion in my formulas please?

 

[Somendra Misra]

I Edited my post , See the suggestion for first formula above, same goes for your second formula also.

Regards,

 

[Asfandyar Ali]

Bingooo. Thank you so much. Genius you are for sure

 

[Somendra Misra]

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,

 

[Asfandyar Ali]

AWESOMENESS