'Google Sheets Split function with Array
I have a table of current work hours in column A. Column B has newly assigned work hours. Column C just sums up the first two columns to show the new work hours. Column B changes based on some other factors in the sheet. I have this mostly working, but the issue I have come across is that sometimes a person will have x+x current hours listed. I am trying to split the numbers in that cell and then sum them together to get the max new hours. The end result should look like this:
| Col A | Col B | Col C |
|---|---|---|
| 40 | 3 | 43 |
| 35 | 5 | 40 |
| 25+3 | 2 | 30 |
I was able to accomplish this using this formula:=IFS(A1="","", ISNUMBER(A1)=TRUE,ADD(A1,B1),ISNUMBER(A1)=FALSE,ADD(SUM(SPLIT(A1,"+")),B1)). However, doing this means I have to drag the formula down all the cells I want to calculate. It works fine that way, but I was hoping to just use an array instead. Is there any way to convert this into an array?
I've tried using this: =ARRAYFORMULA(IF(ISBLANK(A1:A15),"", IF(ISNUMBER(A1:A15)=TRUE,ADD(A1:A15,B1:B15),ADD(SUM(SPLIT(A1:A15,"+")),B1:B15)))) but once it gets to a column containing the + symbol, it spits out an error.
Solution 1:[1]
try:
=INDEX(QUERY(QUERY(SPLIT(A1:A3, "+")*1, "select Col1+Col2"), "offset 1", )+B1:B3)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | player0 |