adding values from different named ranges based on yes/no selected

[georgiboy]

If you are using Excel 365 then you can try something like the below, clear the results that are in range C7:C18 and place the below into cell C7. It is a spill formula so it will populate cells C7:C18 by spilling into them, this is why C7:C18 must be empty before you place the formula into cell C7.

=LET(
  n,FILTER($X$3:$X$32,$W$3:$W$32="Yes"),
  s,DROP(REDUCE("",n,LAMBDA(a,x,HSTACK(a,INDIRECT(x)))),,1),
  d,TAKE(DROP('Enrollment Input'!$A:$A,2),ROWS(s)),
  t,HSTACK(d,s),
  f,HSTACK(d,FILTER(t,TAKE(t,1)=C$6)),
  BYROW($B$7:$B$18,LAMBDA(x,SUM(DROP(FILTER(f,TAKE(f,,1)=x),,1))))
)

Your claims info formula in cell H7 would be similar:

=LET(
  n,FILTER($Y$3:$Y$32,$W$3:$W$32="Yes"),
  s,DROP(REDUCE("",n,LAMBDA(a,x,HSTACK(a,INDIRECT(x)))),,1),
  d,TAKE(DROP('Claims Input'!$A:$A,2),ROWS(s)),
  t,HSTACK(d,s),
  f,HSTACK(d,FILTER(t,TAKE(t,1)=H$6)),
  BYROW($B$7:$B$18,LAMBDA(x,SUM(DROP(FILTER(f,TAKE(f,,1)=x),,1))))
)