CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulaarray-formulassumifs
fluke4
fluke4

Reputation: 121

How to use ABS in SUMIF in ARRAYFORUMULA

I have two Google Spreadsheets that I'm using to track budgets and transactions (I put them both on one sheet to simplify things for this question).

I'd like to use an ARRAYFORMULA function for my SPENT column that will sum all the transactions for a budget in a month, but I want to sum the absolute values of the transactions, not the recorded negative values.

Here is my Spreadsheet: Sample Budget and Transactions in Google Sheets

Link to Spreadsheet

Here is a working formula for the SPENT column that sums the negative values:

={"Spent"; ArrayFormula(IF(LEN(A2:A), SUMIF(F2:F&G2:G, A2:A&J2, H2:H),))}

What I wish worked is this:

={"Spent"; ArrayFormula(IF(LEN(A2:A), SUMIF(F2:F&G2:G, A2:A&J2, ABS(H2:H)),))}

What do I have to do to sum absolute values in an ArrayFormula?

Upvotes: 1

Views: 1256

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(A2:A="",,IFNA(VLOOKUP(A2:A, QUERY(F2:H, 
 "select F,sum(H) 
  where G = '"&J2&"'
  group by F 
  label sum(H)''"), 2, 0), 0)))

0

then "ABS" could be:

0

or your formula:

0

or ABS before summing

0

Upvotes: 2

Related Questions