Converting ARRAYFORMULA to range for SUMIF

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?

• Welcome to Web Applications Stack Exchange. May 25, 2022 at 5:42
• A "range" and an "array" are not the same thing. A range is a reference to a specific location in a spreadsheet and only that location. Once you start to try to process the data in that range, you've formed a virtual array of the data and are no longer referring to the range. Since the `VALUES` of the data in your range are not contained in an actual range of the spreadsheet, `SUMIF` doesn't "know" what to do with them. As you haven't shared a link to the spreadsheet, that's as much specific information as I can provide. May 25, 2022 at 8:45

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).

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")
)
)
``````
• I try this, but the first column has date values so when I import the data the dates get converted from say "2022-05-24" to "44705". How can I prevent this command from running `VALUE` on the date column (column A)? May 25, 2022 at 6:08
• EDIT: I did this myself by just writing a custom App Script function to convert the column with the numbers in the `IMPORTRANGE` data from string to numbers. But, if there is a way to do it all within Google Sheets I am still interested to know. May 25, 2022 at 6:29
• Format column `A` as Format > Number > Date. The format will stick as the data updates. See this answer for an explanation of how date and time values work in spreadsheets. May 25, 2022 at 7:32