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:


when dragged to the right, will become, successively:


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:


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


VLOOKUP Across Several Worksheets (2) – Multiple Search Criteria 8

I recently made the post here, in which I presented a solution to the problem of returning a value based upon matching a single criterion in a given column across multiple worksheets.

In this follow-up post I will look at the analogous case in which we are not matching a single criterion, but several. As mentioned in the first instalment, I will look at two solutions to this problem, one in which we make use of an extra “helper” column in each of the relevant worksheets, and one in which we do without such aids.


VLOOKUP Across Several Worksheets (1) – One Search Criterion 4

Most people with an average level of ability in Excel are perfectly capable of using VLOOKUP when this operation is performed over a range within a single worksheet.

But what happens when we wish to extend our search to multiple worksheets, and so return the first match from whichever sheet happens to be the first which contains our search value(s)?

In this post I will present a solution for such cases in which we have a single criterion to be matched in a given column across multiple worksheets.

In the next instalment (to follow shortly) I will also look at cases in which we are not matching a single criterion, but several. In this situation by far the simplest method is to use an extra “helper” column in each of the relevant worksheets in which we first perform a concatenation of the fields of interest. By doing this we ensure that it is a relatively straightforward case of extending the solution designed for one criterion to work also with multiple criteria.


Collating from multiple sheets based on conditions 5

Some of us may be familiar with the standard technique using INDEX, SMALL, etc. which, given a single-column or single-row array, we can use to return a list of only those values which satisfy one or more criteria of our choosing.

In a previous post (see here) I outlined a method which, given a range consisting of more than one column, returned a single column consisting of all non-blank entries from that range. It can easily be verified that the single condition within this formula (i.e. that the entry be non-blank) can be extended to multiple criteria and so, effectively, we now have at our disposable the means with which to generate single-column lists from both one- and two-dimensional arrays.

But can we go one further yet again? “Three-dimensional” is the collective term often applied to those formulas in Excel which are capable of operating over not just single columns or rows, nor yet ranges consisting of multiple columns or rows (two-dimensional), but which also function effectively over multiple worksheets.


COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges 58

In this post I would like to clear up what appears to me to be a rather widespread misunderstanding of how COUNTIFS/SUMIFS operate, in particular when we pass arrays consisting of more than one element as the Criteria to one or even two of the Criteria_Ranges.

This latter technique is used when the criteria in question are to be considered as “OR” criteria, which is not to be confused with cases where we wish the criteria passed to be calculated rather as “AND” critieria.

For example, given the following data:


Advanced Formula Challenge #5: Results and Discussion 5

Last week I set readers the challenge which can be found here.

This is a reasonably complex problem, and certainly so if we want to present a solution which is relatively concise. However, despite its complexity (and arguably lack of practical use), the solution demonstrates some important techniques for working with strings, and so is not without merit.

The required set-up is as follows: