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:
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)))
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:
05-Jun-2005 19:16
