split answers in 1 cell

[rjmdc]

hi
original formula is =SUM(COUNTIFS(M:M,{650,713,733},O:O,"<>TERM",O:O,"<>PEND"))
i really need a sum that looks like this ex:
11 | 3 | 0
or even better 650:11|713:3|733:0
is that possible?

 

[Sergius]

Hello! You can provide a fragment of your data to understand what you want?

 

[Kaper]

How about such form (it's a spill formula so make sure you have one column on right and 2 rows below the cell empty):

=GROUPBY(M:M,M:M,COUNT,0,0,,(O:O<>"TERM")*(O:O<>"PEND")*ISNUMBER(MATCH(M:M,{650;713;733},0)))

 

[FormR]

Hi, in lieu of a clear set of example data and expected results, here's my guess at what you want.

Book1
	M	N	O	P
1	650			11 | 3 | 0
2	650			650:11|713:3|733:0
3	713
4	713
5	650
6	650
7	713
8	650
9	650
10	650
11	650
12	650
13	650
14	650
Sheet1

Cell Formulas
Range	Formula
P1	P1	=TEXTJOIN(" | ",,COUNTIFS(M:M,{650,713,733},O:O,"<>TERM",O:O,"<>PEND"))
P2	P2	=LET(Arr,{650,713,733},TEXTJOIN("|",,Arr&":"&COUNTIFS(M:M,Arr,O:O,"<>TERM",O:O,"<>PEND")))

 

[rjmdc]

i think P2 is what i am aiming for
lets see if i can make it work