I have a very specific thing I want the Google Sheets to give me, but I'm unsure which approach would be best.
Client ID | Amount | Status | Is delivered |
---|---|---|---|
56691668 | 10.00 USD | Sent | |
56411839 | 15.00 USD | Sent | |
56744744 | 20.00 USD | Sent | |
56691668 | 35.00 USD | Delivered | |
56411839 | 50.00 USD | Delivered | |
56536123 | 60.00 USD | Sent | |
56744744 | 20.00 USD | Sent | |
56733268 | 50.00 USD | Sent | |
56819339 | 75.00 USD | Delivered | |
56813371 | 25.00 USD | Sent | |
56821738 | 15.00 USD | Sent | |
56928364 | 25.00 USD | Sent | |
56691668 | 60.00 USD | Delivered | |
56691668 | 25.00 USD | Delivered | |
56937455 | 20.00 USD | Delivered | |
57001337 | 35.00 USD | Sent | |
56911581 | 75.00 USD | Sent | |
57141905 | 75.00 USD | Delivered |
In the following fictitious table (for context in the 3rd column Status
I have showing Sent
if the Invoice was sent to the client and Delivered
if it was actually paid by the client), so in the 4th Is Delivered
column I want to get a yes
response if a client from the first column has paid - as in Delivered
an amount equal or greater than 100 USD. This could be done with a nested IF function I think, but the problem is, in the first column I also have duplicate values and I want to get the sum of at least 100 USD if a unique client has delivered either all together or fractionally that amount, so I'm not sure what kind of function I could use to turn all of that into a yes
response. Also, I want it to count the sums a client paid only if the 3rd column Status
shows Delivered
.
For example: client 56691668 has Delivered a total of 120 USD, so now I want in the 4th column only to show Yes.
56691668
has four lines. ShouldIs delivered
beyes
for all four lines?