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

[tkaplan]

ETA: using Excel for Microsoft 365 MSO

I have a sheet ("Enrollment Input") with multiple named ranges that have identical column headers (which are user defined in the "Client Setup" tab) and each row of data represents a different month. I then have all the possible named ranges listed in a different sheet with a yes/no dropdown for the user to be able to select which of the named ranges that want included in the totals. Note that the named ranges start with the word "Table" but they are just named ranges, not tables.

If only looking at using one named range of data, I would use this formula:

=INDEX(INDIRECT($X$3),MATCH($B7,'Enrollment Input'!$A$3:$A$75,0),MATCH(C$6,ColumnLabelsEnrollment,0))

This formula is working correctly in that it's looking at the named range that I have in X3, and returning the correct column from my option of column labels based on what they entered in C6 and the correct month in B7.

However, I have all the possible named ranges listed in X3 thru X12. I want the user to enter in column W "yes" or "no" on which ones they want included - so they may say Yes in W3 and W5, which I then want the formula to add up the "MATCH($B7,'Enrollment Input'!$A$3:$A$75,0),MATCH(C$6,ColumnLabelsEnrollment,0)" portion of the formula with the indirect(X3) and indirect(X5). I suppose I can do a whole bunch of nested if statements to check for the "yes"ses but I'm thinking there should be a more sophisticated way to do this, and also leave the door open to add more named ranges in the future. I tried using a sumproduct looking for if column W is yes but that didn't work either....

uploading by workbook. please look at "Plan Totals Exhibit cell C7. This formula works but it will require that I update it each time I add a new named range and I'm hoping for a more sophisticated way to do this that the formula looks thru all of the names in the range instead of one at a time.

goal of this is to end up being a template for other users to be able to populate data, so trying to avoid the user having to run any macros.

Really appreciate any assistance!

[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))))
)

[tkaplan]

Wow! This is perfect and amazing. thank you so much!!! will need to do a little more digging into all of these functions to learn them for future use.
Thank you!!!!