SUMPRODUCT

SUMPRODUCT(array1,array2,array3,...)

SUMPRODUCT is a hidden gem within Excel. It's a function that accountants should get to grips with. You can use it for data analysis where previously you might have thought VBA was required.

Substitute for Lotus 123's XINDEX

Here is an demonstration of how to use Excel functions to create an equivalent for XINDEX.

.

In the above example, I have found how many copies of the Express were sold by the East region.

This link will download a workbook showing two Excel formula constructions that achieve the same result using the Excel functions: INDEX, MATCH and OFFSET. It also includes two VBA procedures that you can use in the same way as the Lotus function. Click here to download.

By the way, I analyse my own expenses using SUMPRODUCT. I have a table of data which records all my expenses. At any point I can find the total of my expenses for this month (very important) or analyse where I have been spending over any period of time that I define.

oOo

Here's an extract from a newsgroup question (1-Jul-2004):

I want to count in the range (A1:D7)
How many CA03 and TR: [the result should be 3]
How many CA03 and ON and OF: [the result should be 5]

Here's a demonstration of my reply:

   Missing: Microsoft Office Web Components
 
This page requires the Microsoft Office Web Components.

Click here to install Microsoft Office Web Components..

This page also requires Microsoft Internet Explorer 5.01 or higher.

Click here to install the latest Internet Explorer.
 

I have shown two possible ways to achieve what you want:

Cells F1, F2 and F3 are used to hold the variables against which you wish to test the data.
Put this formula in a convenient cell (F5 in the above sheet):
=SUMPRODUCT(((A1:A7=F1)*((B1:B7=F2)+(B1:B7=F3)))+((C1:C7=F1)*((D1:D7=F2)+(D1:D7=F3))))

To test CA03 and TR, put CA03 in cell F1, TR in cell F2 and leave cell F3 blank.
To test CA03, ON and OF, put CA03 in cell F1, ON in cell F2 and OF in cell F3.

The second method uses SUMPRODUCT and OFFSET:
Again, Cells F1, F2 and F3 are used to hold the variables against which you wish to test the data.
Put this formula in a convenient cell (F7 in the above sheet):
=SUMPRODUCT((A1:D7=F1)*((OFFSET(A1:D7,0,1)=F2)+(OFFSET(A1:D7,0,1)=F3)))

Unique Entries (RagDyer - microsoft.public.excel 14-Aug-2004)

Here's how to find the number of unique entries in a column or list. Assume the list is in the range A1:A10 then:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

will calculate the number of unique items.

How?
Say your range A1:A10 contains the values:
1,2,3,1,2,2,4,"","",""
The first part of the formula (A1:A10<>"") returns and array of TRUE or FALSE depending on whether the cell contains an entry.
TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE
The second part COUNTIF(A1:A10,A1:A10&"") uses one of the more unusual ways of using COUNTIF. Again it returns an array but this time each value in the array represents a count of the numbers in the array using each value of the array as a criteria.
2,3,1,2,3,3,1,3,3,3
That is, there are two values of 1, three of 2, one of 3 and three of blank.
The TRUE and FALSE array is divided by the count array:
0.5,0.33,1,0.5,0.33,0.33,1,0.33,0.33,0.33
The final three values are ignored (because of the FALSE) leaving
0.5,0.33,1,0.5,0.33,0.33,1
Add this array together and the result is 4.

Published: 01-May-2004
Last edited: 01-Mar-2011 20:52