0

I have two tables, the first table has 6 column and each column has a defined range name as follows:

  1. Col 1: Broker_Name
  2. Col 2: Broker_Market
  3. Col 3: Client_ID
  4. Col 4: Client_Location
  5. Col 5: Client_Risk
  6. Col 6: Client_Amount

The second table organizes and summarizes the data in table 1, so in table two I have the broker name and the brokers market and a third column where it indicates if it is the clients location or the clients risk, and the last column has the value of the location or the risk of the client. The possible values of the third column are LOCATION and RISK.

I want to sum the clients amounts subject to the Broker Name, the Broker Market and the type of value we are taking, LOCATION or RISK, and I'm using the named ranges as an input to the formula.

    =SUMIFS(Client_Amount,Broker_Name,A2,Broker_Market,B2,Client_Risk,C2)

I want the third condition to change depending of the third column in table 2. If it says LOCATION I want to reference the named range Client_Location and if it says RISK I want to reference the named range Client_Risk.

I have an additional table that connects RISK and LOCATION to the named ranges.

1

You can use indirect to reference a named range dynamically. If your =sumifs() formula is in Column D of your Table 2 (I'm assuming) then =SUMIFS(Client_Amount,Broker_Name,A2,Broker_Market,B2,INDIRECT("Client_" & C2),C2) should do the trick.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.