View Full Version : [SOLVED:] How to use SUMPRODUCT for text offset over a discontiguous range? [Tutorial]

Simon Lloyd
04-09-2009, 12:31 PM
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
04-09-2009, 01:02 PM

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


04-09-2009, 01:05 PM
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
04-09-2009, 01:08 PM
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
04-09-2009, 01:14 PM
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
04-09-2009, 01:20 PM
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
04-09-2009, 01:24 PM
Okay, but I need to add a bit of thinking on how to structure, so I will post tomorrow.

04-11-2009, 02:18 PM
Thread moved to it's new home.

Simon Lloyd
04-11-2009, 03:29 PM
Thread closed in lieu of a series of tutorial threads dealing with the construction and understanding of the use of SUMPRODUCT by xld.