Automatic sum of given reference

[PRADEEPB270]

Please look the below mention datas.

Pradeep 100
Amar 200
Sunny 300
Ayush 400
Renu 500
Arushi 600
Pradeep #DIV/0!
Amar 800
Sunny 900
Pradeep #DIV/0!
Pradeep 1,100
Renu 1,200
Pradeep 1,300

Expected Result
Pradeep 2,500

I want automatic calculation when I change the new name,whether apperaring #DIV/0! or not.

Regards

Pradeep Kumar Gupta

[JBeaucaire]

Why not resolve the #DIV/0 error in the the formulas in those cells first?

If the formula were =A1/B1 and B1 were empty or zero, that error would result, but that is easy to fix:

=IF(B1=0, 0, A1/B1)

Once you fix those formulas, a normal SUMIF() will work.

=SUMIF(A:A, "Pradeep", B:B)

[DonkeyOte]

Given use of XL2007 you can use:

B16: =SUMIFS($B$1:$B$13,$A$1:$A$13,$A16,$B$1:$B$13,"<>#DIV/0!")
(where A16 holds Pradeep)

change ranges to suit

[PRADEEPB270]

Please look the below datas:-

Pradeep 100
Amar 200
Sunny 300
Ayush 400
Renu 500
Arushi 600
Pradeep #N#A
Amar 800
Sunny 900
Pradeep #VALUE!
Pradeep #DIV/0!
Renu #NAME?
Pradeep 1,300

Expected Result
Pradeep 1,400

I am applying the following formula,but the expected result is not desireable:-

SUMIFS($B$1:$B$13,$A$1:$A$13,$A16,$B$1:$B$13,"<>#DIV/0!")

Please correct my formula.
My question is if any type of error comes in the datas whether it is #DIV/0! or #VALUE! or any type of errors comes,then,what should be the formula?Please help and suggest.

Best Regards

Pradeep Kumar Gupta

[DonkeyOte]

Pradeep - you've been asked countless times already to follow the rules - there was no need to create a new thread for your follow up given it still relates to this question.
Please ensure you follow the rules going forward else I'm afraid your stay at EF.com will invariably prove to be a short one...

Regards the SUMIFS - you can add additional tests for additional error types.

=SUMIFS($B$1:$B$13,$A$1:$A$13,$A16,$B$1:$B$13,"<>#DIV/0!",$B$1:$B$13,"<>#VALUE!",$B$1:$B$13,"<>#NAME?",$B$1:$B$13,"<>#N/A")

If you prefer to avoid this and you opt against resolving underlying errors (advised - and outlined already by JB) then you will need to use an Array, eg:

=SUM(IF(($A$1:$A$13=$A16)*ISNUMBER($B$1:$B$13),$B$1:$B$13))
confirmed with CTRL + SHIFT + ENTER

[PRADEEPB270]

Thank you for your nice help.I will be highly obliged to you.

Regards

Pradeep Kumar Gupta