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?

More…

Advanced Formula Challenge #12: Results and Discussion 2

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

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

Redimensioning 18

Readers who have read some of my earlier posts will be familiar with the concept of “redimensioning” an array.

This is an extremely useful and important technique, which, in its basic form, allows us to take a two-dimensional array and convert it into one of just a single dimension, whilst of course retaining the elements within that array.

Such an approach is necessary if we wish to further manipulate the entries of some two-dimensional array. For example, we might be in a position in which, for whatever reason, we need to pass each of the entries in a two-dimensional array to an array of one or more parameters for further processing. However, since the evaluation of the resulting multi-dimensional “matrix” is not within Excel’s capabilities, we are obliged to first transform the original array to one of a single dimension.

More…

Grid of Random Integers 3

Inspired by a recent query at one of the Excel forums I occasionally visit, I would like to share a formula-based solution for the task of generating an nxn grid of random integers, where each of those integers is unique within that range.

For example, for the case of n=10, we might have, in A1:J10:

Grid of Random Integers

where I have formatted the cells in this range as custom type: 00 (applying a TEXT function to the formula would complicate matters, in the sense that this would interfere with the functioning of our FREQUENCY construction).

More…

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range 9

I recently received a request from James, who was interested in a formula-based solution to the following problem: given a two-dimensional range containing a mixture of numbers and empty cells (which I am defining as being either “genuinely” empty or as containing the null string “” as a result of formulas in those cells), generate a unique list of those numbers in order of their frequency within that range, with the most frequent first. What’s more, if two or more numbers occur the same number of times within that range, then they should be listed in order of their size from smallest to largest.

For example, for the dataset in A1:F6 below, we would return the list as given beginning in I1.

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range

More…

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

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.

More…

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.

More…

Unique, Alphabetical List from Several Columns 25

In this post I shall present a method for generating a unique, alphabetical list in a single column from data contained within a contiguous range comprising several columns.

For example, given the dataset below in A2:E5, we will return that list beginning in cell G1:

Unique, Alphabetical List from Several Columns

More…

Advanced Formula Challenge #9: Results and Discussion Reply

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

One correct solution received, courtesy of Lori, who not only presented a fine construction for working in Excel 2010 and earlier, but also a 2013 version, which had the added benefit of taking advantage of some of the new (and evidently very useful) features of that version to noticeably abridge the required set-up. So many thanks to Lori for sharing this knowledge and also congratulations on an excellent solution to a particularly complex challenge!

More…

Coercing array returns from CSE-resistant formulas 7

We usually face no problems in cases where we wish to apply a formula to, not just one, but an array of values. And of course we do this by simply committing the formula as an array formula, i.e. with CSE.

However, not all formulas yield so easily, and some stubbornly resist any attempts at coercing an array of returns from them. Here I would like to discuss some techniques which, in addition to array-entry, can help coerce the desired result.

The principal method in such cases is to use a construction involving OFFSET, though a set-up using INDEX is equally viable; indeed, due to its non-volatility, perhaps even preferable. Some cases may require even more coercion than that, and others less. But the one thing they all share in common is that, on its own, array-entry just isn’t enough!

More…

Extracting numbers from a string 4: All numbers to a single cell 2

This is the fourth in a series of discussions on the techniques available for extracting numbers from an alphanumeric string.

In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In the second instalment (here) I looked at extracting consecutive numbers which appear at the end of the string, e.g. 123ABC456.

In the third instalment (here) I looked at extracting all numbers from a string where each of those numbers was to be returned to a separate cell. For example, given the string 81;8.75>@5279@4.=45>A?A; we extracted 81, 8.75, 5279, 4 and 45 into individual cells.

In this post I will look at a technique for extracting all numbers from a string, but where those numbers are to be returned as a single number in a single cell.

More…

Advanced Formula Challenge #8: Results and Discussion 2

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

At the time of writing (Saturday morning, UK time; apologies if anyone has submitted something after that date), two correct solutions received (or three if you count non-Excel-based ones: as he has done for most of the recent challenges, Isai Alvarado produced a solution applicable to Google Sheets, which, as usual, I am unable to verify! So I’m taking your word for it that it’s perfectly correct, Isai! 🙂 ).

The two correct entries came courtesy of Snakehips, who gave a rather lengthy but perfectly correct solution, and John Jairo V, who improved upon his earlier attempt by producing a solution which, in essence, used a similar approach to Snakehips’ but which made use of some very nice technique involving MMULT to considerably abbreviate the required construction. Great work, John!

More…

Extracting numbers from a string 3: All numbers to individual cells 28

This is the third in a series of discussions on the techniques available for extracting numbers from an alphanumeric string.

In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In the second instalment (here) I looked at extracting consecutive numbers which appear at the end of the string, e.g. 123ABC456.

In this post I will demonstrate a technique for extracting all numbers from a string where:

  • The string in question consists of a mixture of numbers, letters and special characters
  • The numbers may appear anywhere within that string
  • Decimals within the string are to be returned as such
  • The desired result is to have all numbers returned to separate cells

More…

Extracting numbers from a string 2: Consecutive numbers at end 3

This is the second in a series of discussions on the techniques available for extracting numbers from an alphanumeric string. In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In this post I will concentrate on techniques for extracting numbers from a string where:

  • The numbers are consecutive
  • The consecutive string of numbers is found at the very end of the string
  • The desired result is to have those consecutive numbers returned to a single cell

As previously, for each of the given solutions, we need to test its soundness in two separate cases: firstly, where there are no numbers elsewhere in the string, e.g. ABC456 and secondly, where there are some numbers elsewhere in the string, either at the start, e.g. 123ABC456, or in the middle, e.g. ABC123DEF456.

More…

Extracting numbers from a string 1: Consecutive numbers at start 8

This is the first in a series of discussions on the techniques available for extracting numbers from an alphanumeric string. Since we often have many different solutions at our disposable for such tasks, I will attempt to present what I feel are the principal candidates and, for each of these set-ups, discuss the merits and potential drawbacks inherent in each.

In the next instalment in this series I shall look at extracting consecutive numbers which appear at the end of the string, e.g. ABC123. In later posts I will deal with cases in which the desired numbers to be extracted are interspersed within the string in groups of one or more, e.g. ABC12DE345-FG6H789, in which case we may be interested in extracting either the number 123456789 into a single cell or each of 12, 345, 6 and 789 into four separate cells.

I shall also consider in future posts cases in which there may be several numbers within a string, though from which we wish to extract perhaps only one (or more) of these numbers, and for which our choice of extraction is based upon one or more criteria. For example, given a string of the form X12-X34-X56-X78-X90 we may wish to develop a technique which extracts the number immediately preceding the fourth occurrence of a hyphen within that string.

More…

Collating from multiple sheets based on conditions 6

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.

More…

Advanced Formula Challenge #7: Results and Discussion 1

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

This is a trickier problem than it at first appears, and indeed there are several pitfalls which prevent us from using more “standard” techniques to arrive at a solution.

Perhaps the two main (hidden) obstacles, which were not immediately obvious from the examples I gave, are, firstly, the fact that we are prevented from using a construction involving a SEARCH-approach (e.g. by locating occurrences of each substring of the four types *????*, †????*, *????† and †????†, as John Jairo V attempted), since this of course presumes that there is only one occurrence of each of those substring types within our string, a presumption which cannot be made.

More…

Converting from base 10 to base x Reply

This is a formula-based solution which, given a positive number in base 10, converts that number into its equivalent form for another, given positive base.

Convert from base 10 to base x

So, given a base-10 value to be converted in A2, and the base to which we wish to convert this value in B2, the required output is generated in C2. Cell D2 contains a “back-check” which re-converts the value in C2 to base 10.

The formula in C2 is:

=SUMPRODUCT(MOD(FLOOR(A2/B2^(ROW(INDIRECT("1:20"))-1),1),B2)*10^(ROW(INDIRECT("1:20"))-1))

More…

Advanced Formula Challenge #6: Results and Discussion Reply

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

This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.

It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.

Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).

More…

List of unique entries from column of space-separated strings 3

Given the list below in A1:A10, we may wish to create a list of unique, single words from that list, as per column B here.

Unique From Space Separated

We can do this with the following set-up: More…