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.
Hope I managed to explain my intent, if not I can further clarify.
Best Answer
I have created this spreadsheet to demonstrate. Note that I have added an extra column
Sum delivered for client
to clarify.So you need to know if a client has payed $100 or more in total, right?
Let's start with that. There are several ways to find out, I've used the DSUM function. For the first row in your spreadsheet (that's row
2
, when headers are included), let's put this in theSum delivered for client
column:Explained:
DSUM
"returns the sum of values selected from a database table-like array or range using a SQL-like query".$A$1:$C$19
is the complete data range including headers - it starts at columnA
row1
, through columnC
row19
.2
is the index of the column we are summing up - theAmount
column.{"Client ID"\ "Status"; A2\ "Delivered"}
- these are the criteria for our query. We want to include only rows which have a client ID equal to the "this" client ID, which is found in cellA2
. And we only want those withStatus
equal toDelivered
. (Note that, depending on your locale, you might have to use commas instead of backslashes, as in{"Client ID", "Status"; A2, "Delivered"}
).Now drag this formula down across all the cells in the
D
column. TheD
column should now display the "delivered sum" for the client.When we have that, it is a simple task to populate the
Is delivered
column (columnE
). ForE2
, enter this formula:This compares the value of cell
D2
("Sum delivered for client") with 100. If it is equal or greater, ayes
is outputted, else an empty string (i.e. nothing).If you don't want the
Sum delivered for client
value to be visible, you could either right-click → Hide column, or combine the two formulas: