Jul 06 2020 03:30 PM
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 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 |
I keep getting stuck. If ID is Unique and "YES" then sum Value while excluding #N/A .
Jul 06 2020 03:53 PM
@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 No | ID | Value |
Yes | 50262146 | 300 |
Yes | 18632031 | 300 |
Jul 06 2020 04:04 PM
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 No | ID | Value |
Yes | 50262146 | 300 |
No | 50484138 | 100 |
Yes | 50262146 | 300 |
Yes | 18632031 | 300 |
Yes | 23501649 | 200 |
Yes | 50262146 | 300 |
No | #N/A | #N/A |
Does that help?
Jul 06 2020 05:45 PM
@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))