google sheets - Split cell with dates and find matching dates on headers - Stack Overflow

admin管理员组

文章数量:1023849

I have a dataset that looks like this:

I put together a shared sheet here.

The formula currently in column E works like this... Count IF:

• Has a "Yes" in columns F and beyond
• Is greater than program start date (ColA)
• Is less than completion by date (ColB)

Here is the current formula:

=BYROW(FILTER(ARRAYFORMULA(IF(ARRAYFORMULA(IF((F1:O1>=A2:A)*(F1:O1<=B2:B),INDEX(F2:O,0)))="Yes",1,0)),A2:A<>""), LAMBDA(x, SUM(x)))

This works great, but what I need to add is a check on column D and increase count IF:

• Date(s) in col D fall between dates in ColA and ColB AND
• Dates weren't already counted for in columns F and beyond. In other words, if they have a "No".

Column C has the expected outcome. I prefer to have one formula fill down the whole column as that will be dynamic. Bonus would be to make this dynamic for columns F and beyond as that will also grow.

Please let me know if there is any other information needed so I can update this post.

I have a dataset that looks like this:

I put together a shared sheet here.

The formula currently in column E works like this... Count IF:

• Has a "Yes" in columns F and beyond
• Is greater than program start date (ColA)
• Is less than completion by date (ColB)

Here is the current formula:

=BYROW(FILTER(ARRAYFORMULA(IF(ARRAYFORMULA(IF((F1:O1>=A2:A)*(F1:O1<=B2:B),INDEX(F2:O,0)))="Yes",1,0)),A2:A<>""), LAMBDA(x, SUM(x)))

This works great, but what I need to add is a check on column D and increase count IF:

• Date(s) in col D fall between dates in ColA and ColB AND
• Dates weren't already counted for in columns F and beyond. In other words, if they have a "No".

Column C has the expected outcome. I prefer to have one formula fill down the whole column as that will be dynamic. Bonus would be to make this dynamic for columns F and beyond as that will also grow.

Please let me know if there is any other information needed so I can update this post.

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Nov 19, 2024 at 11:42 Alma_Matters 1,01411 gold badge22 silver badges1313 bronze badges asked Nov 18, 2024 at 22:56 DanCueDanCue 77211 gold badge99 silver badges2323 bronze badges 4

• I do apologize, but I am having a hard time understanding your expected result for this. What you want and what you currently have is the same. It should fall between the dates in ColA and ColB, the only difference is it should have a "No" on Col F and beyond. With this, you can easily achieve by editing your formula to find the "No", just replace the "Yes" on the formula however even with that, you have a mismatch on the manually inputted Expected result. Please check if you have overlooked a very important detail about your goals. – Babanana Commented Nov 19, 2024 at 6:53
• Thank you for your comment. What I want, the expected outcome, is on column C. Column E currently has a formula that works that takes into consideration the dates on columns A and B for Columns F and beyond. What I need done has to do with column D. I need to add the count for those dates if they also fall between the dates in column A and B and have not already been counted. Hope this helps. – DanCue Commented Nov 19, 2024 at 11:34
• Thank you for replying, I understand your expected output now. I do think that it is what @rockinfreakshow, can you confirm? And If yes, it is good to accept the correct answers for the future readers and the community. – Babanana Commented Nov 19, 2024 at 11:45
• 1 Sure thing. Looks like I accepted his answer as you were typing this. I was taking my time to verify that it worked. – DanCue Commented Nov 19, 2024 at 12:27

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You may try:

=byrow(F2:O,lambda(Σ,if(counta(Σ)=0,,let(Γ,F1:O1,Δ,lambda(x,index(x,row(Σ))),Ξ,lambda(x,y,filter(x,isbetween(y,Δ(A:A),Δ(B:B)))),Λ,split(Δ(D:D),", ",),
 countif(Ξ(Σ,Γ),"Yes")+countif(sort(xlookup(Ξ(Λ,Λ),Γ,Σ,)),"No")))))

Here's another solution:

=MAP(A2:A,B2:B,D2:D,LAMBDA(a,b,d,LET(
  d,IFERROR(SPLIT(d,", ")),r,INDEX(F:ZZZ,ROW(a)),
  IF(a="",,
   SUMPRODUCT(r="Yes",ISBETWEEN(F1:1,a,b))+
   SUMPRODUCT(ISBETWEEN(d,a,b),XLOOKUP(d,F1:1,r,)="No"))))) 

I have a dataset that looks like this:

I put together a shared sheet here.

The formula currently in column E works like this... Count IF:

• Has a "Yes" in columns F and beyond
• Is greater than program start date (ColA)
• Is less than completion by date (ColB)

Here is the current formula:

=BYROW(FILTER(ARRAYFORMULA(IF(ARRAYFORMULA(IF((F1:O1>=A2:A)*(F1:O1<=B2:B),INDEX(F2:O,0)))="Yes",1,0)),A2:A<>""), LAMBDA(x, SUM(x)))

This works great, but what I need to add is a check on column D and increase count IF:

• Date(s) in col D fall between dates in ColA and ColB AND
• Dates weren't already counted for in columns F and beyond. In other words, if they have a "No".

Column C has the expected outcome. I prefer to have one formula fill down the whole column as that will be dynamic. Bonus would be to make this dynamic for columns F and beyond as that will also grow.

Please let me know if there is any other information needed so I can update this post.

I have a dataset that looks like this:

I put together a shared sheet here.

The formula currently in column E works like this... Count IF:

• Has a "Yes" in columns F and beyond
• Is greater than program start date (ColA)
• Is less than completion by date (ColB)

Here is the current formula:

=BYROW(FILTER(ARRAYFORMULA(IF(ARRAYFORMULA(IF((F1:O1>=A2:A)*(F1:O1<=B2:B),INDEX(F2:O,0)))="Yes",1,0)),A2:A<>""), LAMBDA(x, SUM(x)))

This works great, but what I need to add is a check on column D and increase count IF:

• Date(s) in col D fall between dates in ColA and ColB AND
• Dates weren't already counted for in columns F and beyond. In other words, if they have a "No".

Column C has the expected outcome. I prefer to have one formula fill down the whole column as that will be dynamic. Bonus would be to make this dynamic for columns F and beyond as that will also grow.

Please let me know if there is any other information needed so I can update this post.

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Nov 19, 2024 at 11:42 Alma_Matters 1,01411 gold badge22 silver badges1313 bronze badges asked Nov 18, 2024 at 22:56 DanCueDanCue 77211 gold badge99 silver badges2323 bronze badges 4

• I do apologize, but I am having a hard time understanding your expected result for this. What you want and what you currently have is the same. It should fall between the dates in ColA and ColB, the only difference is it should have a "No" on Col F and beyond. With this, you can easily achieve by editing your formula to find the "No", just replace the "Yes" on the formula however even with that, you have a mismatch on the manually inputted Expected result. Please check if you have overlooked a very important detail about your goals. – Babanana Commented Nov 19, 2024 at 6:53
• Thank you for your comment. What I want, the expected outcome, is on column C. Column E currently has a formula that works that takes into consideration the dates on columns A and B for Columns F and beyond. What I need done has to do with column D. I need to add the count for those dates if they also fall between the dates in column A and B and have not already been counted. Hope this helps. – DanCue Commented Nov 19, 2024 at 11:34
• Thank you for replying, I understand your expected output now. I do think that it is what @rockinfreakshow, can you confirm? And If yes, it is good to accept the correct answers for the future readers and the community. – Babanana Commented Nov 19, 2024 at 11:45
• 1 Sure thing. Looks like I accepted his answer as you were typing this. I was taking my time to verify that it worked. – DanCue Commented Nov 19, 2024 at 12:27

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You may try:

=byrow(F2:O,lambda(Σ,if(counta(Σ)=0,,let(Γ,F1:O1,Δ,lambda(x,index(x,row(Σ))),Ξ,lambda(x,y,filter(x,isbetween(y,Δ(A:A),Δ(B:B)))),Λ,split(Δ(D:D),", ",),
 countif(Ξ(Σ,Γ),"Yes")+countif(sort(xlookup(Ξ(Λ,Λ),Γ,Σ,)),"No")))))

Here's another solution:

=MAP(A2:A,B2:B,D2:D,LAMBDA(a,b,d,LET(
  d,IFERROR(SPLIT(d,", ")),r,INDEX(F:ZZZ,ROW(a)),
  IF(a="",,
   SUMPRODUCT(r="Yes",ISBETWEEN(F1:1,a,b))+
   SUMPRODUCT(ISBETWEEN(d,a,b),XLOOKUP(d,F1:1,r,)="No"))))) 

本文标签： google sheetsSplit cell with dates and find matching dates on headersStack Overflow