Shorten a long formula

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Is there a way to make the following ARRAY formula simpler?
=SUM(('ALL A-C'!$I$26:$I$1525=Setup!$I$31)*('ALL A-C'!$N$26:$N$1525=E5)*'ALL
A-C'!$K$26:$K$1525)+SUM(('ALL A-C'!$I$26:$I$1525=Setup!$I$32)*('ALL
A-C'!$N$26:$N$1525=E5)*'ALL A-C'!$K$26:$K$1525)+SUM(('ALL
A-C'!$I$26:$I$1525=Setup!$I$33)*('ALL A-C'!$N$26:$N$1525=E5)*'ALL
A-C'!$K$26:$K$1525)+SUM(('ALL A-C'!$I$26:$I$1525=Setup!$I$34)*('ALL
A-C'!$N$26:$N$1525=E5)*'ALL A-C'!$K$26:$K$1525)
Rob
 
One way ("_" substituted for the space char to prevent confusing
linewrap). This is not array-entered:

=SUMPRODUCT((('All_A-C'!$I$26:$I$1525=Setup!$I$31) +
('All_A-C'!$I$26:$I$1525=Setup!$I$32) +
('All_A-C'!$I$26:$I$1525=Setup!$I$33) +
('All_A-C'!$I$26:$I$1525=Setup!$I$34)),
--('All_A-C'!$N$26:$N$1525=E5), 'All_A-C'!$K$26:$K$1525)

If you name your "All A-C" ranges, it becomes even simpler:

=SUMPRODUCT(((rngI=Setup!$I$31) + (rngI=Setup!$I$32) +
(rngI=Setup!$I$33) + (rngI=Setup!$I$34)), --(rngN=E5), rngK)
 
Thanks J.E.
I used the formula with the range names as you suggest and it works just
great!
Rob
 
J.E., I have a couple of questions regarding that formula.
1. Can you explain how this really works especially the double negative?
2. The formula I now have is as below....

=SUMPRODUCT((ItemCodes=Setup!$I$31) + (ItemCodes=Setup!$I$32)
+(ItemCodes=Setup!$I$33) + (ItemCodes=Setup!$I$34),--(MonthTaxInvRec=D$5),
Debit)

"MonthTaxInvRec" is a column named range of months.

Instead of "MonthTaxInvRec=D$5", how do I get the formula to give me the
total
based on ....AND(DateRange<=E39,DateRange>=D39)
where DateRangeis a column named range of dates?
Rob
 
1. First, SUMPRODUCT() ignores text and boolean (TRUE/FALSE) values.
Since the first 5 arrays return TRUE/FALSE values, they need to be
coerced to numeric 1/0, respectively. Addition of the first 4 does
that, and creates a single array that has zero where all component
arrays were FALSE and a positive number where any component array
had TRUE. Likewise, a unary minus will coerce the fifth array from
TRUE/FALSE to -1/0. The second unary minus inverts the -1 to +1.

The combined array from the first four terms, the fifth array and
Debit are all multiplied together. Where both the combined array and
the fifth array are 1, the resulting value is the value from Debit
(i.e., 1 x 1 x (debit)). If the value in either array is zero, the
resulting array value will be zero.

The result of the multiplication is then added.

2. One way:

=SUMPRODUCT((ItemCodes=Setup!$I$31) + (ItemCodes=Setup!$I$32)
+(ItemCodes=Setup!$I$33) + (ItemCodes=Setup!$I$34),
--(DateRange>=D39),--(DateRange<=E39), Debit)
 
Awww! That's so good. Thanks J.E (especially for the explanation).
Everything you've explained is excellent and it will certainly help me if I
need to use this formula elsewhere. AND ....
"--(DateRange>=D39),--(DateRange<=E39)" amendment works great!
My effort as...
"--AND(DateRange>=D39,DateRange<=E39)" was a flop!
Rob
 
Back
Top