Lookup across a row and use offset to disregard last cell with data

ogwt

New Member
Joined
Oct 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello

I was helped out is another forum to get an excel formula which would lookup across a row and find the last column/cell with data, then count the number of cells with any data in them, but not including the last column.

The formula goes in Cell A2 and starts to read across the row from Cell F2 thru X2
This adds a "1" in cell A1, where data is present from F2:W2 due to the offset function, or a "zero" where there is not data between F2:W2
This data is dynamic and the number of columns increases daily.

Formula that works perfect in excel is
Excel Formula:
=IF(COUNT(OFFSET(F2,0,0,1,LOOKUP(2,1/(F2:X2>0),COLUMN(2:2))-1))>0,1,0)

But this does not work in Google sheets?
Image attached how it works in excel

Any thoughts?

Thanks in advance.
 

Attachments

  • Screenshot.png
    Screenshot.png
    8.6 KB · Views: 21

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sorry, made a mistake there,
The part in red is correct now as Cell A2 and Not A1:

"This adds a "1" in cell A2, where data is present from F2:W2 due to the offset function, or a "zero" where there is not data between F2:W2"
 
Upvote 0

Forum statistics

Threads
1,223,377
Messages
6,171,743
Members
452,419
Latest member
mapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top