How to use SUMPRODUCT for text offset over a discontiguous range? [Tutorial]

[Simon Lloyd]

How do you successfully calculate multiple ranges and criteria using
SUMPRODUCT, i have just started using SUMPRODUCT on a regular basis and starting to
understand the power of using it, i do find difficulty calculating figures for an offset
of text over non contiguous ranges is there an easy way to achieve it or remember how to
formulate it?

This will be the base question for an ongoing tutorial kindly offered by xld, for those of you that don't know xld (www.xldynamics.com (http://www.xldynamics.com)) has written the definitive tutorial and example uses around SUMPRODUCT rather than using array formulae that need to be committed with Ctrl+Shift+Enter.

[Bob Phillips]

Simon,

It depends how non-contiguous the range is.

If it is every nth column (or row) it is very simple. Then you SUMPRODUCT across all columns (rows) and use the MOD function to isolate the columns (rows) that you are interested.

For instance, let us assume that the range to be tested is A2:Z2, and we have say a date, then a name, then an amount, you can sum the amounts for say Simon like so


=SUMPRODUCT(--(MOD(COLUMN(A2:Z2),3)=2),--(A2:Z2="Simon"),B2:AA2)

[Benzadeus]

On the same site, there is also one of the Excel's articles that I most like: http://www.xldynamic.com/source/xld.LastValue.html

It is about different ways of finding last value/text in column by formulas or macros, and an impressive benchmark.

[Simon Lloyd]

Is it possible to SUMPRODUCT over sectioned ranges? i.e
=SUMPRODUCT(--(MOD(COLUMN(A2:Z2,E10:J30),3)=2),--(A2:Z2="Simon",E10:J30="Simon"),B2:AA2,F10:AA10), i'm thinking if i had data in block ranges throughout my worksheet, of course the ranges could be named.

[Bob Phillips]

I have a technique for very ragged ranges, but in the situation that you include, I would think it simpler to do the two ranges as separate SPs, and add them.

[Simon Lloyd]

Bob, for the benefit of others (well mine really ;)) could you take us step by step throught building a sumproduct formula, i.e what we must include and what we can include, and perhaps maybe the rules around what you intend to sum, e.g i notice in the formula you supplied you use the double urinary twice, i think it coerses the text to a value but not entirely sure of its function or when to use it.

[Bob Phillips]

Okay, but I need to add a bit of thinking on how to structure, so I will post tomorrow.

[lucas]

Thread moved to it's new home.

[Simon Lloyd]

Thread closed in lieu of a series of tutorial threads dealing with the construction and understanding of the use of SUMPRODUCT by xld.