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.
