Formula too long

  • Thread starter Thread starter sfletcher
  • Start date Start date
S

sfletcher

Hi:

The following formula looks at data in 9 ranges on a worksheet, an
compares that data to information in cell B3 (a date) and t
information in another range (G5:AF5).


SUM(('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")*('Shee
A'!G13:AF13<>""))+SUM(('Sheet A'!$G$6:$AF$6<=$B$3)*('Shee
A'!$G$5:$AF$5="P")*('Sheet A'!G18:AF18<>""))+SUM(('Shee
A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")*('Shee
A'!G19:AF19<>""))+...

This formula works, but I'm getting a 'formula too long' error for som
of the longer worksheet names. Is there a shorter way to get this jo
done?

Thanks for your help
 
If it's because of worksheet names being too long, then you can put short
named ranges in place. like 'a' will refer to the range Sheet A!G6:AF6.
 
Thanks, but I don't think that will work - I've got too many worksheets
and their names are in other links and formulas. Is there another way
 
The following formula looks at data in 9 ranges on a worksheet, and
compares that data to information in cell B3 (a date) and to
information in another range (G5:AF5).

SUM(('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")*('Sheet
A'!G13:AF13<>""))+SUM(('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet
A'!$G$5:$AF$5="P")*('Sheet A'!G18:AF18<>""))+SUM(('Sheet
A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")*('Sheet
A'!G19:AF19<>""))+...
Click to expand...

1. Eliminate unnecessary SUM calls. You could rewrite this as

SUM(('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")
*('Sheet A'!G13:AF13<>""),('Sheet A'!$G$6:$AF$6<=$B$3)
*('Sheet A'!$G$5:$AF$5="P")*('Sheet A'!G18:AF18<>""),
('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")
*('Sheet A'!G19:AF19<>""),...)

2. Eliminate redundancy. Each of the summand terms includes the multiplicative
terms ('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P"). They only need to
appear once. So another rewrite.

SUM(('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")*(
('Sheet A'!G13:AF13<>"")+('Sheet A'!G18:AF18<>"")+('Sheet A'!G19:AF19<>"")+..))

3. Exploit any common aspects of ranges with OFFSET. Yet another rewrite.

=SUM(SUMPRODUCT(('Sheet A'!$G$6:$AF$6<=$B$3)*('Sheet A'!$G$5:$AF$5="P")*
(T(OFFSET('Sheet A'!G1:AF1,{13;18;19;...}-1,COLUMN(INDIRECT("G:AF"))-7,1,1))
<>"")))

Note: SUM, SUMPRODUCT and T function calls are all necessary.
 
Back
Top