I've created an example spreadsheet

CodePudding user response：

One way is to use query(), like this:

=arrayformula( query( 
  if( islogical(B9:E), B9:E   0, B9:E ), 
  "select 
     Col1, 
     sum(Col3) / count(Col3), 
     sum(Col4) / count(Col4) 
   where Col1 is not null 
   group by Col1 
   label 
     sum(Col3) / count(Col3) 'Called Progress', 
     sum(Col4) / count(Col4) 'Registered Progress' 
   format 
     sum(Col3) / count(Col3) '0%', 
     sum(Col4) / count(Col4) '0%' ", 
  1 
) )

This gives a table like this:

CodePudding user response：

use:

=BYROW(B3:B6, LAMBDA(x, SUMPRODUCT(B9:B=x, D9:D)))

and:

=BYROW(B3:B6, LAMBDA(x, SUMPRODUCT(B9:B=x, E9:E)))