How to find a value from a range of cells and place at a specific in google sheet

Question

Suppose I have only one value anywhere at the cell range C2:Z2, I want that value at B2. What can I do?

I need this solution for all the rows bellow this also. The value might at C:C column at one row, at H:H column at another, that means it is dispersed at the range but there will be only one value at the range in a row.

Answer 1

Place this formula in B2:

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(C2:Z), , COLUMNS(C2:Z)))))

---

The formula above works for any type of values.

If your values are numbers then a simpler formula could be used (MMULT does row wise sum here):

=MMULT(
  ARRAYFORMULA(--(C2:Z)),
  SEQUENCE(COLUMNS(C2:Z), 1, 1, 0)
)

Answer 2

if there will be only one value in the row then a simple sum function will do the job for you. put this in B2 ..

=sum(C2:Z2)

Answer 3

You can use FILTER()

=FILTER(C2:Z2, NOT(ISBLANK(C2:Z2)))

Reference:

• FILTER()