Create a dynamic 'if' statement in Excel w

Question

* Updated *

I have a rather large excel data set that I'm trying to summarise using up to 3 dimensions: region, sector, industry. Any combination of these dimensions can be set or left blank and I need to create a formula that accommodates this WITHOUT using VBA.

Within the data I've set up named ranges to refer to these dimensions.

I'm using an array formula but I'd like to dynamically create a string which is then used as the boolean argument in the array formula.

For instance if:

A1 = "Hong Kong" (region)
B1 = <blank> (sector)
C1 = <blank> (industry)

I create a dynamic string in D1 such that

D1 = (region="Hong Kong")

I then want to use the string in D1 to create an array formula

E1 = {counta(if(D1,employees))}

However, if the user includes a sector such that:

 A2 = "Hong Kong" (region)
 B2 = "finance" (sector)
 C2 = <blank> (industry)

Then I want the string in D2 to update to:

D2 = (region="Hong Kong")*(sector="finance")

Which then automatically updates the value in E2 which still has the same formula.

E2 = {counta(if(D2,employees))}

Is this possible? Alternatively is there any other way of achieving the same outcome, keeping in mind that I need to be able to copy D1 and E1 down into different rows so that different combinations of the dimensions can be viewed simultaneously.

Thanks.

* Updated *

To be clear, the reason the I need the values in column D to be dynamic is so that I can create different scenarios in Row 1, Row 2, Row 3 etc. and I need the values in column E of each row to match the criteria set in columns A:C of that row.

Answer 1

There had to be a fairly simple way!

Columns B:D contain the criteria, A is a criterion number and E is the result of applying the DSUM function to the criterion in that row. I've used DSUM as it seems more natural (to me at least) to sum employee numbers. However, DCOUNT can equally well be used. For brevity I've not shown the data I'm using but it is a very trivial data set with just a few rows of test data.

The first set of criteria in row 2 is: Sector takes value of "Man" (manufacturing) whilst Region and Industry are unspecified. The 3rd set of criteria (in row 4) is: the Region is "Fr" (for France) AND the Industry is "Cars". The results in the DSUM column are obtained by applying the set of criteria in the corresponding row. All, some or even none of the cells in a row may contain entries.

The approach used is based on columns G:J, where with the exception cells G1 and G2 (which contain the numbers 0 and 1, respectively) everything in these columns has been generated by a formula.

There are twice as many rows in columns G:J as there are sets of criteria listed in B:D and the rows should be taken in pairs. The first pair (rows 1 and 2) provide a criterion table for use in DSUM corresponding to the first set of criteria (the table is cells H1:J2), the second pair in rows 3 and 4 provides a criterion table for the second set of criteria (cells H3:J4), etc. (Ignore the 11th row - I copied too many rows downwards in the screenshot!)

Column G has a fairly obvious pattern and can be generated by applying a simple =IF() function in cell G3 which references the starting pair in G1 and G2 with the formula in G3 then copied downwards.

The cells in columns H:J reference the appropriate cells of the set of all criteria (B1:D6 in the screenshot) using the INDEX function (and making use of the value in column Galong the way). It is not too difficult to create a single formula that can be copied from H1 to the range H1:J11 by judicious use of mixed relative and absolute addressing and an IF or two). Note that references to an empty cell in B2:D6 will generate a value of 0 in the corresponding cell in H:J so the construct IF(x=0,"",x) must be used - this makes the formula used in the cells in columns H:J a bit clunky but not excessively so.

Having generated the 5 criteria tables corresponding to the 5 sets of criteria in B:D, use is made of the OFFSET function to deliver the correct criterion table as the third argument of the DSUM functions in column E.

I chose to base my OFFSETs on cell $H$1, so the top-left cell of the criterion table for the first set of criteria is offset from my base cell by 0 rows and 0 columns. The second criterion table is offset by 2 rows and 0 columns, the third by 4 rows and 0 columns. It should be clear how the number of offset rows and columns to use can be calculated from the corresponding criterion number in column A. It should also be obvious that the final two arguments of the OFFSET function will always be 2 and 3. So my DSUM() functions in column E look something like

=DSUM(myData,"Employees",OFFSET($H$1,row_offset,0,2,3))

where myData is the named range containing the test dataset and row_offset is a very simple formula involving the corresponding value in column A.

It would have been nice to have been able to deliver the third argument of the function without having to adopt the approach of effectively reproducing the sets of criteria in B1:D6 in cells H1:J10. Whilst there are ways to generate the required criterion table arrays formulaically without putting them onto the worksheet, I found that DSUM generated an error when applying such an array as its third argument.