# Nested IFs and SUMIFs for numbers and text in Google Sheets

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.

• Client `56691668` has four lines. Should `Is delivered` be `yes` for all four lines? – Vidar S. Ramdal Sep 6 '18 at 12:30

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 the `Sum delivered for client` column:

``````=DSUM(\$A\$1:\$C\$19; 2; {"Client ID"\ "Status"; A2\ "Delivered"})
``````

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 column `A` row `1`, through column `C` row `19`.
`2` is the index of the column we are summing up - the `Amount` 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 cell `A2`. And we only want those with `Status` equal to `Delivered`. (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. The `D` 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 (column `E`). For `E2`, enter this formula:

``````=IF(D2 >= 100; "yes"; "")
``````

This compares the value of cell `D2` ("Sum delivered for client") with 100. If it is equal or greater, a `yes` 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:

``````=IF(DSUM(\$A\$1:\$C\$19; 2; {"Client ID"\ "Status"; A2\ "Delivered"}) >= 100; "yes"; "")
``````

simply paste this formula into 4th column:

``````=IFERROR(ARRAYFORMULA(IF(VLOOKUP(A2:A;
QUERY(QUERY(QUERY(A2:C;
"select A, B, C where C = 'Delivered'");
"select Col1, sum(Col2) group by Col1 label sum(Col2) ''");
"select Col1 where Col2 >= 100");
1; 0); "yes"; )); )
``````

it's based on a triple query where each query is fed by sub-query. first `QUERY` picks only those rows which have `Delivered` in the 3rd column. the second `QUERY` sums all amounts per unique client and third `QUERY` checks if any client paid `100` or more USD. this triple query is then compared to range `A2:A` in `VLOOKUP` formula and `IF` there is a match `yes` is printed. `ARRAYFORMULA` then assures that formula is fully automated (eg. there is no need to put 1 formula per every row in 4th column) and all is wrapped in `IFERROR` formula to filter out all the noise