Google-sheets – How to sum all positive values in Google Sheets based on whether the associated data in another column is unique

google sheetsgoogle-sheets-arrayformula

I have a spreadsheet that my company is trying to transfer into Google Sheets from Excel. I've used an array formula to accomplish this result in Excel, but it's not translating to Sheets and I know very little about arrays in Sheets. Here is the data scenario:

In column A are names of clients. Some clients have multiple accounts and each account has a separate balance in my list of data, however, many clients have only one account. The client name appears in column A for every account they have in column B. So if they have three accounts, they appear three times in column A. The account names are in column B. The balances are in column C and can be either positive or negative depending on account activity from month to month.

I need to sum all positive balances for all clients with only one account. This will be workable by itself, but if I could also add all positive net balances for clients with more than one account as well, that would be ideal.

I need this formula to ensure that our net payments to clients, less the result of this formula, equals our total balance for all accounts for the month. Since we process net payments to our clients with more than one account, I can’t simply add up all positive balances. It has to be all positive balances for only those clients with one account. We have anywhere from 250 to 300 accounts to process each month, so being able to double check our balances is really helpful.

The formula that works in Excel (for just positive balances for owners with one account) is:

{=SUMPRODUCT(D2:D350,(D2:D350>=0)*(COUNTIF(B2:B350,B2:B350)=1))}

If someone could help me out with a formula that will work in Sheets and explain a bit about how to convert the formula from Excel, I'd really appreciate it.

Best Answer

I stumbled onto the answer myself, but attached is a sample data sheet in case this helps anyone else with a similar need.

The trick was taking the ending row reference "350" out from all ranges.

https://docs.google.com/spreadsheets/d/1-dxpI3GpLAxkoMyscPRsCwkaec1CWRuX5-5Ej0DjVTo/edit?usp=sharing

Related Solutions

Google-sheets – Sum the totals for each product over all sheets in a spreadsheet

When you have many sumif formulas in your spreadsheet, this is usually a sign you need to use query instead. The script you are currently using to fetch the sheet names can be modified so that it creates a query formula instead:

function queryFormula() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formula = "=query({";
  var sheets = ss.getSheets();
  for (var i=1; i<sheets.length; i++) {
    formula = formula + "'" + sheets[i].getName() + "'!A5:E;";
  }  
  formula = formula.replace(/;$/, '}, "select Col1, Col2, sum(Col5) group by Col1, Col2")');
  ss.getSheetByName("Totales").getRange("H2").setFormula(formula);
}

The result of running this script is that cell H2 gets the formula

=query({'02/26/2016 20:33:08 gcrosta@gmail.com'!A5:E;'02/26/2016 19:23:17 leyla.s.rodriguez@gmail.com'!A5:E;'02/26/2016 17:26:37 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:26 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:17 iris2908@fibertel.com.ar'!A5:E;'02/26/2016 17:05:49 iris2908@fibertel.com.ar'!A5:E}, "select Col1, Col2, sum(Col5) group by Col1, Col2")

which does everything else. The first parameter of the query is the array, which consists of the ranges A5:E put together, on top of one another.

The second parameter is a query string, which says to sum column 5 (which in the range A:E is E), grouping by columns 1, 2 (which are A,B).

The output looks like this:

+-------------------------------------------------+------+------+
| ACEITE DE oLIVA Escencias de la Tierra x 500 ml | 2215 |  582 |
| Aceite de Coco God Bless 325 g                  | 2488 |  900 |
| Aceite de Coco Refinado Napus 660 ml            | 2626 |  840 |
| Aceite de Coco Virgen God Bless 225 g           | 2479 |  900 |
| Aceite de Coco Virgen God Bless 500 g           | 2387 | 1500 |
+-------------------------------------------------+------+------+

Updating the formula

If you rename the script function "queryFormula" into onOpen, it will run every time you open the spreadsheet, thus ensuring you get the current set of sheets in the formula.

Google Sheets Data Validation – How to Ensure All Columns Have Unique Data Within the Column

Well, after a bit of fiddling, I found a solution that currently works:

Specify the cell range as desired: for the whole columns, starting with column A and ending with column BB, so it is

A1:BB

Then the custom formula should be:

=countif(A$1:BB,"="&A1)=1

It seems like the Data Validation operation the places this "validation formula" (which must be True to avoid a warning) in every cell, as if a regular formula were copy/pasted to the cell. Which might allow a bit of intuition as to when and how to place the $ in the cell references, if true. It seems as if the formula should be written as if for the top left cell of the range specified.

Best Answer

Related Solutions

Google-sheets – Sum the totals for each product over all sheets in a spreadsheet

Updating the formula

Google Sheets Data Validation – How to Ensure All Columns Have Unique Data Within the Column

Related Topic