Month Count in Google Sheets.

[savvysum]

Hi,
I'm looking for an excel formula that can count the months from the start of positive #s to the end of positive #s.
Zero values in between positive #s count.
Zero values do not count before the first positive # or after the last positive #.

Client	5/31/2022	4/30/2022	3/31/2022	2/28/2022	1/31/2022	12/31/2021	11/30/2021	10/31/2021		Formula Answer
A	0	1	2	1	4	0				4
B	2	1	0	0	1	0	0			5
C	0	6	1	0	0	0	0	2		7

Mr Excel

lookup range of dates 2.00%,6/1/2020,3/31/2022,date,int rate 3.00%,4/1/2022,6/30/2023,Jul-20,2.00% 3.92%,7/1/2023,12/31/2023,Aug-20,2.00% 5.33%,1/1/2024,12/31/2024,Sep-20,2.00% Oct-20,2.00% Nov-20,2.00% Dec-20,2.00% Jan-21,2.00% Feb-21,2.00% Mar-21,2.00% Apr-21,2.00% May-21,2.00% Jun-21,2.00% Ju...

docs.google.com

I appreciate your time.

 

[Fluff]

How about

=LET(f,FILTER(COLUMN($B$1:$I$1),B2:I2<>0),MAX(f)-MIN(f)+1)

 

[savvysum]

Hi Fluff, I have an "Unknown function: 'LET'."

 

[Fluff]

Are you sure you are using 365 as LET has been around for quite a while.

 

[savvysum]

I'm using google sheets. How do I translate for google sheets?

 

[Fluff]

That is something that you make very clear right from the start, Excel & Sheets are NOT the same thing.
Also questions regarding sheets should be posted in the General Discussion & Other Applications section of the board.
I have done that for you this time.

 

[Fluff]

How about

=MAX(FILTER(COLUMN($B$1:$I$1),B2:I2<>0))-MIN(FILTER(COLUMN($B$1:$I$1),B2:I2<>0))+1

 

[savvysum]

thanks that worked for google sheets

 

[Fluff]

You're welcome & thanks for the feedback.