How Sum function works even while there is an error in datasets

[vaishaks89]

Hi All,

I am here with a query, which I found quite interesting. Recently, a guy in our office posted in the workgroup about how to use the Sum function even when there is an error in the datasets. Can someone explain how this works? He seems to be using arrays. I will paste the below post:

"Have you ever tried to sum a column of numbers, but the SUM formula doesn't work because there's an error in the data set?

There's a way to SUM the column even if it contains an error by using an array formula.

If the data is in Column B, use this formula in cell B5: =SUM(IF(ISERROR(B1:B4),0,B1:B4)). Make sure you press the Control, Shift & Enter keys at same time instead of only hitting Enter. You'll know you did it correctly if you see your formula surrounded by { and }."

 

[xlstime]

Hey vaishaks89,

There are lots of way to sum range if it contains an error -

Array function

1) =SUM(IFERROR(G35714:G35717,0)) with ctl+shft+enter
2)

Using array we would execute the complex calculation into one cell only


3) If you are using Excel 2010 and above than use AGGREGATE

=AGGREGATE(9,6,G35714:G35717).

How AGGREGATE works -
First value - 9 is for function number like

Second value - 6- is for skip / ignore value like -



Than finally range


You can google it for more options.

For array batter understanding, please refer below

https://support.office.com/en-us/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Hi All,

I am here with a query, which I found quite interesting. Recently, a guy in our office posted in the workgroup about how to use the Sum function even when there is an error in the datasets. Can someone explain how this works? He seems to be using arrays. I will paste the below post:

"Have you ever tried to sum a column of numbers, but the SUM formula doesn't work because there's an error in the data set?

There's a way to SUM the column even if it contains an error by using an array formula.

If the data is in Column B, use this formula in cell B5: =SUM(IF(ISERROR(B1:B4),0,B1:B4)). Make sure you press the Control, Shift & Enter keys at same time instead of only hitting Enter. You'll know you did it correctly if you see your formula surrounded by { and }."

 

[bosco_yip]

Or this non-array formula to sum data and in ignore error values.

=SUM(SUMIF(B1:B4,{">0","<0"}))

Regards
Bosco

 

[xlstime]

Yes this one too, but this is partial array formula

Or this non-array formula to sum data and in ignore error values.

=SUM(SUMIF(B1:B4,{">0","<0"}))

Regards
Bosco

 

[vaishaks89]

Hi guys,
Thanks a ton for giving so much answers.
I tried the partial array formula and the aggregate function. Both worked fine.
And thanks for the handy explanation from @xsltime and @bosco_yip. Thanks guys