Converting ARRAYFORMULA to range for SUMIF

Question

I have a sheet that IMPORTRANGE's data from another spreadsheet, which contains a column with numerical values that are of type string. In another sheet I am trying to sum the values in that column using SUMIF. The command is the following:

=SUMIF(Transactions!G2:G, "Food & Dining", ARRAYFORMULA(VALUE(Transactions!D2:D)))

Where column G contains text to check, and column D contains the numbers of type string. Of course, I need those values to be numbers to sum them up, so I added the ARRAYFORMULA(VALUE(...)) to try to make it a range, but it tells me that the "Argument must be a range". I'm fairly new to Google Sheets, so I'm confused as to why doesn't it consider this a valid range? When I use that ARRAYFORMULA(VALUE(...)) command on its own it returns a range of values, so how come in SUMIF its not valid? Is there a way to achieve this, or maybe some easier way by adding a rule that every cell in D be converted to a number?

Answer 1

See my comment to your original post. That said, there are other functions that could be used to obtain the result you are after. For instance:

=ArrayFormula(MMULT(SEQUENCE(1,ROWS(Transactions!D2:D),1,0),IF(Transactions!G2:G="Food & Dining",Transactions!D2:D*1,0)))

MMULT performs Matrix MULTiplication. If you don't know what that is, it is too complex to explain here. But all you need to know for your purposes is that it's an alternative to SUMIF as you are trying to use it. Adapting the components should be obvious.

The *1 in the formula is converting strings to numbers (and if numbers already exist, it will just return the number anyway).

Answer 2

The sumif() function requires a range. You should apply value() already when importing the data, like this:

=arrayformula( 
  iferror( 
    value(importrange("...", "Sheet1!A1:D")), 
    importrange("...", "Sheet1!A1:D") 
  ) 
)