0

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?

2
  • Welcome to Web Applications Stack Exchange. May 25 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.
    – Erik Tyler
    May 25 at 8:45

2 Answers 2

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

1

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") 
  ) 
)
3
  • 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)?
    – Cizox
    May 25 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.
    – Cizox
    May 25 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 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.