0
274
ViewsGet maximum string of semicolon-separated values in a cell
I am trying to sum all the values that belong to a squads that have been completed in a sprint. I have source table that contains 3 columns (Squad Name, Value, Sprint). The Sprint column has cells that contain semicolon-separated lists.
What I am trying to achieve is sum all values in Column B where the criteria are:
- Squad name = Tigers
- MAX Sprint = Sprint 11
I would like to sum those values in Column B where the max sprint value in Column C is Sprint 11. I would not like to include those values where the max value in Column C is sprint 12.
At the moment I have got: =SUMIFS(B2:12,A2:12,"Tigers",C2:C12,...)
Can someone help me figure out the last bit? Thanks
3 answers
Answer question0
If you want to sum only Sprint 11 having squad name tigers then try-
=QUERY(ArrayFormula(SPLIT(FLATTEN(SPLIT(FILTER(C2:C8,A2:A8="Tigers"),";"))," ")),"select sum(Col2) where Col2=11 label sum(Col2) ''")
0
use:
=SUMPRODUCT(QUERY(SPLIT(FLATTEN(B1:B&"×"&TRIM(SPLIT(C1:C, ";"))), "×"),
"select Col1 where Col2 = 'Sprint 11'"))
0
If you always expect to have exactly three sprints formatted as shown, you can use the following query to get the sum of all rows where the Squad Name is "Tigers" and the highest Sprint value in that row is exactly 11.
=QUERY({A2:A,B2:B,ARRAYFORMULA(INT(SPLIT(REGEXREPLACE(C2:C,"[a-zA-Z ]",""),";")))},"select sum(Col2) where Col1='Tigers' and (Col3>=11 or Col4>=11 or Col5>=11) and not (Col3>=12 or Col4>=12 or Col5>=12) label sum(Col2) ''")
If you expect that the number of sprints might change (for example, a cell in column C with the value "Sprint 2;Sprint 3;Sprint 11;Sprint 12") then this formula will work instead.
=QUERY({FILTER(A2:A,NOT(ISBLANK(C2:C))),FILTER(B2:B,NOT(ISBLANK(C2:C))),QUERY(TRANSPOSE(QUERY(TRANSPOSE(ARRAYFORMULA(IF(ISBLANK(C2:C),,INT(SPLIT(REGEXREPLACE(C2:C,"[a-zA-Z ]",""),";"))))),"select "®EXREPLACE(JOIN("",ARRAYFORMULA(IF(LEN(C2:C),"max(Col"&ROW(C2:C)-ROW(C2)+1&"), ",""))),"..\z",""))),"select Col2")},"select sum(Col2) where Col1='Tigers' and Col3=11 label sum(Col2) ''")