In column A are names of clients. Some clients have multiple accounts and each account has a separate balance in my list of data, but 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.
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 I came up with is:
{=SUM(SUMIFS(C2:C350,C2:C350,">0",A2:A350,COUNTIF(A2:A350,A2:A350)=1))}
This formula only returns 0 no matter what. I have also tried this without wrapping the SUMIFS in a SUM function (which also returns a 0), but all the research I’ve done points to the version above being correct. I’m hoping to understand my mistake, not just be provided with a formula that works.
Can anyone explain what I’ve done wrong?
