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…

Counting Rows Where Condition Is Met In At Least One Column 42

In this post I would like to present a solution to the situation in which we wish to count the number of rows for which a stipulated condition is met in at least one of several columns.

To illustrate what is meant by this, consider the extract below:

Counting Rows Where At Least One Condition Is Met

which details levels of scrap nickel exports for various countries and for various years (you can download the workbook here).

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…

Shortest Formula Challenge #2: Results and Discussion 5

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

5 correct solutions received, courtesy of John Jairo V, GreasySpot, Bill Szysz, James and ChrisBM (who actually missed off a final parenthesis in his formula, though I will be lenient here!). So well done to all!

As to whose was the shortest, excluding the offering from Isai Alvarado, who beat everyone with his 51-character (excluding the equals sign) Google Sheets construction (well done Isai!), that accolade is shared by John and Bill, both of whose solutions came in at 56 characters, which is quite a remarkable coincidence when you consider that each used a completely different construction! So congratulations to John and Bill!

More…

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

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:

More…