google sheets formula to count years and year ranges presented different ways - Stack Overflow

admin管理员组

文章数量:1313756

I'm looking for an array formula that can count the number of years in each cell from a single column. Here's a link to a shared google sheet showing the input data in column E and the desired output from a formula.

example input:

[1992-1993]

the output would be - 2

[1995-1997]

the output would be - 3 (1995, 1996, 1997)

[1995, 1997]

the output would be - 2

[1992-1993, 1994-1996, 1997]

the output would be - 6 (1992, 1993, 1994, 1995, 1996, 1997)

Thank you!

I'm looking for an array formula that can count the number of years in each cell from a single column. Here's a link to a shared google sheet showing the input data in column E and the desired output from a formula.

https://docs.google/spreadsheets/d/1eL2CTScKB8DGFI7pFbYnEsgljyEOysrW1IpXJAXtrTc/edit?usp=sharing

example input:

[1992-1993]

the output would be - 2

[1995-1997]

the output would be - 3 (1995, 1996, 1997)

[1995, 1997]

the output would be - 2

[1992-1993, 1994-1996, 1997]

the output would be - 6 (1992, 1993, 1994, 1995, 1996, 1997)

Thank you!

• google-sheets
• formula
• counting

Share Improve this question Follow edited Jan 31 at 18:10 Patsytalk 1,11711 gold badge11 silver badge1515 bronze badges asked Jan 31 at 5:05 MartyMarty 4144 bronze badges 1

• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Jan 31 at 5:17

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

You may try:

=map(E2:E,lambda(Σ,if(Σ="",,reduce(,split(Σ,", ",),lambda(a,c,a+rows(sequence(right(c,4)-left(c,4)+1,1,left(c,4))))))))

Try this alternative approach

=BYROW(E2:E, LAMBDA(cell,IF(cell<>"",SUM(ARRAYFORMULA(IF(REGEXMATCH(TEXT(SPLIT(cell, ", "), "0"), "-"),VALUE(RIGHT(SPLIT(cell, ", "), 4)) - VALUE(LEFT(SPLIT(cell, ", "),4)) + 1,  1))),"")))

Input data - Years	Desired output - counting the number of years
1992-1993	2	2
1987, 1989, 1993-1994, 1996-1997, 1999-2002, 2004-2006, 2008-2009, 2011-2012	17	17
1981-1983	3	3
1981-1982	2	2
1983-1985, 1987, 1990-1991, 1994-1998, 2000-2002	14	14
1981-1982	2	2
1981	1	1
1982	1	1
1983	1	1
1975-1977	3	3
1977-1978	2	2
1978	1	1
1979	1	1
1976	1	1
1982	1	1
1980-1982	3	3
1980-1981, 1983	3	3
1981-1983	3	3
1980-1981, 1983	3	3
1981	1	1
1977, 1980	2	2
1978-1979	2	2
1978-1979	2	2
1980	1	1
1980-1982	3	3
1983	1	1
1981, 1983	2	2
1982-1983	2	2
2004-2005	2	2
1988-1989, 1993-1994, 1996, 1999, 2001, 2004-2006	10	10
1986-1987, 1989-1990, 1992-1993, 1995, 1997, 1999, 2001, 2003	11	11
1986-1987, 1996	3	3
1984	1	1
2000	1	1
2001-2003	3	3
1992-1995	4	4
1990-1993	4	4
1993	1	1
2000	1	1
2001-2003, 2005	4	4
1986-1987	2	2
1988-1990	3	3
1984, 1997	2	2
1996	1	1
1990, 1992-1995, 1997-1998, 2001	8	8
1986	1	1
1986	1	1
1985-1987, 1989, 1991, 1994, 1997	7	7
1983-1985	3	3
1990	1	1
1985	1	1
1988, 1990	2	2
1984, 1986	2	2
2002-2004	3	3

Here's another solution:

=MAP(E2:E, LAMBDA(x, 
   IF(x = "", ,
     LET(
       s, SPLIT(x, ", "), 
       SUMPRODUCT(RIGHT(s, 4) - LEFT(s, 4) + 1)
     )
   )
 ))

For each row, we are splitting by the comma, then for each value, we are subtracting the leftmost 4 digits from the rightmost 4 digits and adding 1, finally we are summing the results.

本文标签： google sheets formula to count years and year ranges presented different waysStack Overflow