Dynamic Excel Formula Based on Another Cell Value

Hello,
excel dynamic formula.jpeg

I want to change my formula which is the selected cell in the D column, based on criteria that can be selected from the green underlined cell in the B column. These criteria are related to data in the red-underlined column in the formula. Here is the question. When I select different criteria from my "Kriter" dropdown list, I want to change the formula to automatically corresponds to the related column address. For example, "AltBölge" option is related to H2:H cells while other options are related to A2:A or B2:B.

This

=ÇOKEĞERSAY(Veri!F2:F;"HEDEF";Veri!H2:H;C13)

should turn automatically to this:

=ÇOKEĞERSAY(Veri!F2:F;"HEDEF";Veri!A2:A;C13)


Note: =ÇOKEĞERSAY equals to =SUMIFS

Thanks in advance

Not necessary to upload the worksheet actually. (Simplifying it is a huge issue.)

I want to change dynamically the column reference in a formula based on another cell value.

Example:

Let assume F1 is Criteria Cell.

If F1 is "gender" then;
=SUMIFS(A2:A;"old";B2:B;"male")

If F1 is "marital status" then;
=SUMIFS(A2:A;"old";C2:C;"married")

How I can get these automatically updated functions?

Thanks

ÇOKEĞERSAY is actually COUNTIFS not SUMIFS.

Do you have headers on the Veri sheet that match the values in the criteria cell?

Originally Posted by furkanyasar

......
....

Note: =ÇOKEĞERSAY equals to =SUMIFS

@Furkan;

The function "ÇOKEĞERSAY" is "COUNTIFS" in English version.

Try to use this formula;

Please Login or Register  to view this content.

What other choices are you likely to have in the criteria cell (F1). If you have more than 2 then you could use an INDEX function to determine which column to search through, and if the criteria (i.e. Male, Married etc.) is linked to the drop-down choice in a table then you could return that through a VLOOKUP formula, so your formula would basically be:

=COUNTIFS(A:A,"old",INDEX_to_return_range,VLOOKUP_to_return_criteria)

Hope this helps.

Pete