Sum Multiple Cells Ignoring Error if found in one of the Cells

[ssanjju]

Hi All,

I am having a challenge in summing up multiple cells when there is a error in one of the cells. I require a standard formula that can ignore the error found in one of cells and sum all the available values in other cells. The values in the allocated, completed and pending cells for each of the agents are vlooked from another workbook and so if an agents was not allocated work from one of the buckets then there is no value found and the vlook up formula throws throws the #N/A error. But I would like to still sum the completed values for the other buckets those were allocated to the agent.

Please find attached the simple spreadsheet with the table wherein I have tried explaining the desired result. Please check and provide your valuable support and thank you in advance for the help.

Test.xlsx

[Ace_XL]

Use this array formula

=SUMPRODUCT(($B$2:$J$2="Completed")*IFERROR($B4:$J4,0))

Confirm with Ctrl+Shift+Enter and not just Enter

[ssanjju]

Thank you so much, it worked solving my challenge as I exactly required.

[Tony Valko]

Here's another one...

=SUMIFS(B3:J3,B$2:J$2,"Completed",B3:J3,">0")

Assuming no negative values.