How do I get the Total Sales data to match the latest monthly sales data available in each row?

[Chastitycaprice]

Hello everyone, 

I would like the last column below (Total Sales $) to match the latest monthly sales data for each row, ignoring the purchase qty column information (Example: Jan Pur Qty, Feb Pur Qty, and Mar Pur Qty column).

To clarify, for row #1, I would like the total sales cell to reflect the Feb Sales data, $1,444.50.

A second example, the total sales for row #3, would match the sales data listed for March, $7,792.70 since there is an entry there. 

How would I achieve the above objectives with Excel formulas?

[#2]

@Chastitycaprice 

You can leverage the "Search from last to first" option of XLOOKUP to find the last non-empty cell:

https://exceljet.net/formulas/get-value-of-last-non-empty-cell

In your case ,you can use below formula:

=LET(
    sales_data, FILTER(
        A2:J15,
        ISNUMBER(FIND("Sales $", A1:J1, 1)),
        ""
    ),
    BYROW(
        sales_data,
        LAMBDA(row, XLOOKUP(TRUE, row <> "", row, "", 0, -1))
    )
)

See attached sheet.

Also, could you please paste sample data in a table format, instead of posting screenshot here?

I have to use below "Picture From File" to convert your screenshot into something I can work on. It involves lots of manual fiddling.

 

[#3]

@Chastitycaprice 

A 365 solution. I see you're working in a table, so my solution includes structured references.

=LET(
    sales, DROP(WRAPROWS(Table1[@[Dec Pur QtA]:[Mar Sales $]], 2), , 1),
    MAX(sales)
)

Also, you may want to make certain all your numbers are being treated as such.  If you click those green tags in the sheet, it may offer to convert numbers stored as text to numbers.

[#4]

Hi @Chastitycaprice 

365 variant with data in Table1

=TAKE( TOCOL( WRAPROWS(Table1[@[Dec Pur Qty]:[Mar Sales $]], 2), 1), -1)

[#5]

Thank you! I was able to get the result I wanted primarily.

I wanted to hide the zeros and the blanks. The below formula did the job.
=XLOOKUP(1,(N4:V4<>"")*(N4:V4<>0),N4:V4,,,-1)

Right now, I'm having issues with replacing the N4:V4 with the specific cells that I want to pull data from N4, P4, R4, T4, V4. I want to avoid specific cells.

How do I insert the above cells using commas into the below formula? I tried to this multiple times but I'm getting errors. I've attached sample data.

=XLOOKUP(1,(N4:V4<>"")*(N4:V4<>0),N4:V4,,,-1)

[#6 · Accepted solution]

@Chastitycaprice 

Use HSTACK:

=XLOOKUP(1, (HSTACK(N4, P4, R4, T4, V4) <> "") * (HSTACK(N4, P4, R4, T4, V4) <> 0), HSTACK(N4, P4, R4, T4, V4), , , -1)