SumIF excluding duplicates and Errors

Occasional Contributor

Hello,

 

I am having trouble writing the formula to only capture unique values, with multiple columns.

 

I know that the answer is- 900

Yes or NoIDValue
Yes50262146300
No50484138100
Yes50262146300
Yes18632031300
no23501649200
Yes50262146300
No#N/A#N/A



I keep getting stuck. If ID is Unique and "YES" then sum Value while excluding #N/A .

3 Replies

@hurshie , can you clarify? 

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 NoIDValue
Yes50262146300
Yes18632031300

@TheAntony 

 

Yes apologies, i was in a rush and set this up incorrectly.

 

I cant figure out how to sum all the highlighted in red is 800

As 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

 

300+300+200  = 800

 

Yes or NoIDValue
Yes50262146300
No50484138100
Yes50262146300
Yes18632031300
Yes23501649200
Yes50262146300
No#N/A#N/A



Does that help?

@hurshie, let me know if this is what you are looking for...

 

=SUM(FILTER((C2:C8)/COUNTIFS(A2:A8,"Yes",B2:B8,B2:B8),(A2:A8="Yes")*NOT(ISERROR(B2:B8)),0))