Formula too long

[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

 

[Dave R.]

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.

 

[sfletcher]

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

 

[Harlan Grove]

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<>""))+...

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.

 

[sfletcher]

Problem fixed - thanks a lot for your help, Harlan and Dave.