• 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.

Sumif while error in range

coolsac12

Member
I want to sum a range while error in range basis on condition. Please see the attached file...
 

Attachments

  • Sumif_error.xlsx
    9.4 KB · Views: 18
This is a non-array solution

=AGGREGATE(9, 6, Range)

9 means Sum
6 means ignore errors

eg: =AGGREGATE(9,6,G5:G11)
 
Hi:
@Hui , I Guess OP want conditional sum ignoring error, Aggregate formula will just ignore the error, it won't do criteria based sum.

Thanks
 
Hi coolsac12,

Here is another one {array}
=SUM(IF(A1:A14=D4,IFERROR(B1:B14,0)))

NON-ARRAY:
If you make a helper column say column C, enter this in C1:
=IFERROR(--B1,0)
Copy down till C14

now you can use this with just enter:
=SUMIF(A1:A14,D4,C1:C14)

Regards,
 
Oh, I missed the simple:

=SUMIFS(B1:B14,A1:A14,D4,B1:B14,"<>#VALUE!",B1:B14,"<>#DIV/0!")

with just enter

add more criteria like:
=SUMIFS(B1:B14,A1:A14,D4,B1:B14,"<>#VALUE!",B1:B14,"<>#DIV/0!",B1:B14,"<>#N/A")

Regards,
 
Back
Top