Incrementing Indirect Column References Within SUMIF(S)/COUNTIF(S) 13

Most Excel users are aware that, when a formula containing relative column references is copied to further columns, those references are updated accordingly. So, for example, the formula:

=SUMIFS(C:C,$A:$A,"X",$B:$B,"X")

when dragged to the right, will become, successively:

=SUMIFS(D:D,$A:$A,"X",$B:$B,"X")
=SUMIFS(E:E,$A:$A,"X",$B:$B,"X")

etc., etc.

And so we have a relatively (no pun intended) simple means by which we can obtain a conditional sum from successive columns.

But what if the range we wish to increment is being referenced indirectly? For example, what if we are using a version of the above, but in which the sheet being referenced is dynamic, viz:

=SUMIFS(INDIRECT("'"&$A$1&"'!C:C"),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

where A1 contains the sheet name (e.g. “Sheet1”) which is to be referenced at any given time?

(You can download the workbook here.)

Unfortunately, when we drag this formula to the right, the ranges being referenced indirectly do not change whatsoever. Of course, this is quite expected, since those “ranges” are not ranges at all, merely text strings masquerading as ranges; it is only by virtue of their being passed to INDIRECT that they are then interpreted as actual ranges.

What to do then? How can we amend the first formula such that, as it is copied to the right, we obtain, successively, the equivalent of:

=SUMIFS(INDIRECT("'"&$A$1&"'!D:D"),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")
=SUMIFS(INDIRECT("'"&$A$1&"'!E:E"),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

etc., etc.?

Well, this too turns out to be reasonably straightforward, and we have the luxury of being able to choose from either an INDEX- or an OFFSET-based solution. Of course, the fact that our construction already necessitates the use of INDIRECT arguably renders our choice here – which under normal circumstances would almost always favour INDEX – a moot one, since volatility cannot be avoided in any case.

That said, I tend to prefer the use of INDEX to OFFSET where possible, even if the resulting set-up is perhaps a touch less intuitive, viz:

=SUMIFS(INDEX(INDIRECT("'"&$A$1&"'!A:XFD"),,COLUMNS($A:C)),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

and copied to the right, the point being that, when we pass a zero (or, equivalently, omitted) value as INDEX’s row_num (or column_num) parameter, we return a reference to the entire column (or row), as required. Hence, and assuming that A1 contains e.g. “Sheet2”, the part:

INDEX(INDIRECT("'"&$A$1&"'!A:XFD"),,COLUMNS($A:C))

will resolve to:

=INDEX(Sheet2!A:XFD,,3)

which is:

Sheet2!C:C

And, when this construction is copied to the right, thus becoming:

=SUMIFS(INDEX(INDIRECT("'"&$A$1&"'!A:XFD"),,COLUMNS($A:D)),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

which is:

=SUMIFS(INDEX(Sheet2!A:XFD,,COLUMNS($A:D)),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

and, since:

COLUMNS($A:D)

the only difference between the two versions, is this time 4, we have:

=SUMIFS(INDEX(Sheet2!A:XFD,,4),Sheet2!A:A,"X",Sheet2!B:B,"Y")

which is:

=SUMIFS(Sheet2!D:D,Sheet2!A:A,"X",Sheet2!B:B,"Y")

and so on and so on.

Despite my personal preference for INDEX, however, it should be noted that, as will be seen, we are not always in a position in which we are able to employ that function as an alternative to OFFSET. As such, readers may well prefer to accustom themselves to OFFSET-based set-ups for the sake of consistency.

And that set-up would here be:

=SUMIFS(OFFSET(INDIRECT("'"&$A$1&"'!A:A"),,COLUMNS($A:B)),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

the point being that the part:

INDIRECT("'"&$A$1&"'!A:A")

in this case is:

Sheet2!A:A

and, when we OFFSET this range by a number of columns equivalent to the result of:

COLUMNS($A:B)

i.e. 2, we return a reference to an entire column two columns to the right of that passed to OFFSET, i.e.:

Sheet2!C:C

So far, so good, then. But what if our indirect referencing refers, not to a single sheet, but to many? For example, what if we have a multiple-worksheet, 3D version of the above, i.e.:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!B:B"),"Y"))

where Sheets is defined as, for example:

={"Sheet1","Sheet2","Sheet3","Sheet4"}

Just as previously, we would hope that, when dragged to the right, our construction would become the equivalent of:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!D:D"),INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!B:B"),"Y"))
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E:E"),INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!B:B"),"Y"))

etc., etc.

The problem here is the apparent need to generate the series of text strings “B:B”, “C:C”, “D:D”, etc. Indeed, a quick internet search for this issue would seem to suggest that the only previous attempts at solving this problem are ones which employ rather ugly-looking and lengthy constructions to this end such as:

SUBSTITUTE(ADDRESS(1,COLUMNS($A:C),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,COLUMNS($A:B),4),1,"")

which takes disproportionately convoluted measures in order to generate the three-character string “C:C”.

In fact, such convolution is not necessary, since our OFFSET set-up above is readily adaptable to work for such 3D situations as well (though it should be noted that, unfortunately, no equivalent INDEX-based construction can be construed for such cases).

Firstly, however, I would like to point out that, if the required construction is one which employs SUMIF – and not SUMIFS, COUNTIF or COUNTIFS – then, thanks to a peculiar property of that function not shared by its siblings, we can, if we wish, employ an even more straightforward solution than that which uses OFFSET. So if, in place of the above SUMIFS construction, we instead wished to generate a copiable equivalent to:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!C:C")))

then we could use the simple though not-so-intuitively-obvious:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!"&CELL("address",C:C))))

The rather surprising point is that, with consideration to SUMIF, we are not actually required to generate a text string which represents an entire column reference (C:C). As readers can easily verify, the construction:

CELL("address",C:C)

does not return an array of 1048576 addresses, viz:

{"$C$1","$C$2","$C$3",...,"$C$1048576"}

but rather the single entry:

"$C$1"

(In fact, in order to coerce the aforementioned array of returns (not that I can imagine such a requirement in practice!), we would need to go to rather extreme lengths, such is the stubbornness of the CELL function when it comes to generating more than a single output: for example:

CELL("address",OFFSET(C1,N(IF(1,{0,1,2})),))

appropriately coerced, will return the array:

{"$C$1","$C$2","$C$3"}

etc.)

Note that we could also use:

CELL("address",C$1)

here, since that too will resolve to:

"$C$1"

But how does this help us here? Of what use is a reference to a single cell when we require an interrogation of an entire column’s worth?

Indeed, if we continue the resolution of our construction, we see that it is equivalent to:

=SUMPRODUCT(SUMIF(INDIRECT({"'Sheet1'!A:A","'Sheet2'!A:A","'Sheet3'!A:A","'Sheet4'!A:A"}),"X",INDIRECT({"'Sheet1'!$C$1","'Sheet2'!$C$1","'Sheet3'!$C$1","'Sheet4'!$C$1"})))

which, resolving the indirect references, is effectively:

=SUMPRODUCT(SUMIF({Sheet1!A:A,Sheet2!A:A,Sheet3!A:A,Sheet4!A:A}),"X",{Sheet1!C1,Sheet2!C1,Sheet3!C1,Sheet4!C1}))

which in turn is simply equivalent, as most readers are undoubtedly aware, to performing the four individual constructions:

=SUMIF(Sheet1!A:A,"X",Sheet1!C1)
=SUMIF(Sheet2!A:A,"X",Sheet2!C1)
=SUMIF(Sheet3!A:A,"X",Sheet3!C1)
=SUMIF(Sheet4!A:A,"X",Sheet4!C1)

and then summing the results of each to arrive at our desired total.

But hang on, I hear you say, what about the issue of that last reference? Isn’t it summing just a single cell (C1) in each sheet? What’s more, how is that consistent with the fact that the criteria_range is not a single cell, but an entire column (A:A)?

Well, yes, this is true. However, SUMIF possesses the rather useful property that, if the requirement that the two ranges (range and sum_range) be of an equal dimension is not satisfied, then the sum_range is redimensioned such that its dimensions match that of the range.

Note that this applies equally whether the size of the sum_range is greater or less than that of the range. The important point to note, however, is that any redimensioning is done such that the lower range reference in the sum_range remains fixed; in other words, in order to match the dimensions of the range, the sum_range is extended “downwards”.

Perhaps some examples will help clarify:

=SUMIF(A2:A11,"X",C2)

is equivalent to:

=SUMIF(A2:A11,"X",C2:C11)

and:

=SUMIF(A2:A11,"X",C3)

is equivalent to:

=SUMIF(A2:A11,"X",C3:C12)

(Note the change in end row reference here: the range (A2:A11) here comprises 10 cells; as such, so will the redimensioned sum_range. Since that range begins in C3. the range to be considered after redimensioning will be C3:C12 (and not C3:C11)).

and, finally:

=SUMIF(A2:A11,"X",C1:C11)

is equivalent to (amongst many others):

=SUMIF(A2:A11,"X",C1:C100)

It should be pointed out that this syntax has the downside that it renders the SUMIF construction volatile. So, for example:

=SUMIF(A2:A11,"X",C2)

is volatile, whereas:

=SUMIF(A2:A11,"X",C2:C11)

is not.

However, given that we are in any case employing this technique within a construction which already comprises INDIRECT, it could be argued that the debate as to whether to introduce further volatility is a moot point.

A nice, concise solution to 3D constructions employing SUMIF, then. But what about our original query, which was to find an equivalent, copiable version of:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!B:B"),"Y"))

which employs SUMIFS, and not SUMIF?

Unfortunately, attempting the “trick” I have just outlined, i.e.:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!"&CELL("address",C:C)),INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!B:B"),"Y"))

results in a #VALUE! error, presumably since the aforementioned redimensioning of the single cell reference resulting from our CELL construction ($C$1) into the necessary full-column reference (C:C) is a feature exclusive to SUMIF and hence of no avail to us here.

Fortunately, as mentioned, our OFFSET set-up is still perfectly viable, viz:

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&Sheets&"'!A:A"),,COLUMNS($A:B)),INDIRECT("'"&Sheets&"'!A:A"),"X",INDIRECT("'"&Sheets&"'!B:B"),"Y"))

the deconstruction of which is almost identical to that for the single-sheet version given previously, and which I leave to the reader.

Another post to follow shortly. Watch this space!

13 comments

  1. As usual, excellent food for thought …

    I, too …, am very pleased to read you again …!!!

    Cheers ๐Ÿ™‚

    James

  2. XOR LX – Good to see your posting again – was worried that you had disappeared like Daniel (of Excel Hero)

  3. Interesting article – it’s a little frustrating there isn’t a simple worksheet function equivalent to the Range.Address property in VBA.

    For returning full column references here’s another way i recently stumbled upon:

    =REPLACE(CELL("address",(C1,C:C)),1,LEN(CELL("address",C1))+1,"")

    C1 is a dummy reference and can refer to the current cell or could be replaced by C:C or any cell selection on the same sheet.

    This method also supports multiple area ranges [eg (A:A,B2,C1:D3) instead of C:C] and should work ok in different regions.

  4. @Lori

    Very creative!

    So the inputs for CELL’s info_type parameter are universally set to the English-language variant across all Excel versions?

    Cheers

  5. Pingback: Shortest Formula Challenge #6: Results and Discussion « EXCELXOR

  6. Yes CELL and INFO functions universally accept English versions of arguments.

    See: http://www.rondebruin.nl/win/s9/win013.htm

    Lots of useful info there – though one minor amendment in the lookup section is to use a Greek or Japanese character as lookup value to get the last text entry. The suggested MATCH(“*”,A1:A10,-1) doesn’t work for text beginning with symbols such as #,-,& (,) or empty strings (since they are lower down the unicode sort order than *.)

  7. I had this formula in the “Total” sheet to calculate the total sales per month per employee from all sheets (North, South, East, West):

    Jan Feb Mar
    Alex 500 600 650

    =SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&TO&"'!AL:AL"),,COLUMNS($AL:AM)),INDIRECT("'"&TO&"'!B:B"),$B4))

    In which “TO” is the name defined for North, South, East, West sheets

    Column AL to AZ in each sheet contains monthly sales data (with column header Jan/Feb/Mar/etc.)

    Column B in each sheet contains the name of employees.

    However, the formula picks up the blank column AL from the “Total” sheet, not from N/S/E/W sheets defined in “TO” hence I got zero result. What did I do wrong? Could you help, please please please.

    Thank you,

    Tina

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s