SumIF excluding duplicates and Errors

Question

Hello,&nbsp;I am having trouble writing the formula to only capture unique values, with multiple columns.&nbsp;I know that the answer is- 900Yes or NoIDValueYes50262146300No50484138100Yes50262146300Yes18632031300no23501649200Yes50262146300No#N/A#N/AI keep getting stuck. If ID is Unique and "YES" then sum Value while excluding #N/A .

theantony · Answer

hurshie&nbsp;, can you clarify?&nbsp;Unique Yes is only 2 rows and add up to 600. Am I thinking about this wrong since I don't see the 900 you get?Yes or NoIDValueYes50262146300Yes18632031300

hurshie · Answer

TheAntony&nbsp;&nbsp;Yes apologies, i was in a rush and set this up incorrectly.&nbsp;I cant figure out how to sum all the highlighted in red is 800As you can see below. I am trying to write a function that states If first column states YES, ID is unique, Sum the value to equal 800. I want it to ignore the duplicate ID's and #N/A's&nbsp;300+300+200&nbsp; = 800&nbsp;Yes or NoIDValueYes50262146300No50484138100Yes50262146300Yes18632031300Yes23501649200Yes50262146300No#N/A#N/ADoes that help?

theantony · Answer

hurshie,&nbsp;let me know if this is what you are looking for...&nbsp;=SUM(FILTER((C2:C8)/COUNTIFS(A2:A8,"Yes",B2:B8,B2:B8),(A2:A8="Yes")*NOT(ISERROR(B2:B8)),0))

Forum Discussion

SumIF excluding duplicates and Errors

3 Replies

Resources

Yes or No	ID	Value
Yes	50262146	300
No	50484138	100
Yes	50262146	300
Yes	18632031	300
no	23501649	200
Yes	50262146	300
No	#N/A	#N/A