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
CodePudding user response：
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) ''")
CodePudding user response：
use:
=SUMPRODUCT(QUERY(SPLIT(FLATTEN(B1:B&"×"&TRIM(SPLIT(C1:C, ";"))), "×"),
"select Col1 where Col2 = 'Sprint 11'"))
