Sumproduct "read easy"

[David]

Greetings and TIA for any help you can give
I would like to make my sumproduct formulas more readable by referencing out
to criteria in other cells. An example:

To sum the contents of named field 'Quantity' where the named field 'fruit'
is contained in the list 'LookupFruits' i have
=sumproduct(--(not(isna(match(fruit,LookupFruits,0)))),Quantity)
this works ok but gets hard to read when other conditions are added to the
formula.

I would prefer something like this (non working example):
=sumproduct(--indirect("Condition1"),Quantity) where Condition1 is a named
cell containing <not(isna(match(fruit,LookupFruits,0))))>
I could have a list of conditions and adjacent descriptions. This would make
for "easy" reading of the worksheet.
Any ideas?

 

[Myrna Larson]

I don't think this would be possible, at least with your example.

You have a SUMPRODUCT formula, and the piece you want to move to another cell
is actually an array formula, where you are running MATCH for every fruit in
the range, not just 1 fruit. To separate this, you would have to enter the
array formula as a "normal" array formula, i.e. into a range of cells with one
row for each row in the database/list.

If you enter an array formula into a single cell, you'll only return the
result of the MATCH for the first fruit in the list.

Array formulas are intended to solve complex problems that can't be solved by
regular formulas. The ability to calculate the result at all is the top
priority; "readability" isn't an issue.

If you want readability, you would have to write VBA code that can interpret
your more readable arguments, then (in this case) either construct the array
formula in code and execute it, or execute the MATCH formula in a loop.

You will probably find the speed penalty unacceptable, as well as the time it
takes to write and debug a VBA procedure.

 

[Aladin Akyurek]

Extra evaluations, even if feasable, mean loosing speed. Better avoided with
formulas that operate on computed arrays.

BTW, Isn't...

=sumproduct(--(not(isna(match(fruit,LookupFruits,0)))),Quantity)

identical to:

=sumproduct(--isnumber(match(fruit,LookupFruits,0)),Quantity)

 

[Dave Gallagher]

Aladin,
Thanks for your helpful response
The database i'm searching has up to 4 fields being searched and up to 20k
records. Would it be feasible to load this into a VBA array in memory (i have
1GB) and operate with WorksheetFunctions?
Thanks again

 

[Aladin Akyurek]

It looks like you have a performance problem. If so, two possible options
are:

(a) Try to convert the SumProduct or 'array' formulas into SumIf formulas by
concatenating appropriate fields in the data area;

(b) Try to apply such formula to relevant subranges instead of 20,000 cells.
This can be achieved by sorting data in ascending order on some relevant
field/column and computing subranges using Match formulas the results of
which can be used subrange specifiers.