Formula is too long.

  • Thread starter Thread starter Gary Bernard
  • Start date Start date
G

Gary Bernard

I am sure there is a way, but I have tried 'Jan 04'!:'Dec
04'! at the beginning of the appropriate terms, instead of
summing the individual months, without success. Is there
a way to shorten the following formula, which Excel will
not accept due to its length, or am I going to have to put
parts of it in different cells? Probably a very basic
simple thing, but self teaching can be soooooo aggravating
with the missing basics one doesn't have.
 
-----Original Message-----
I am sure there is a way, but I have tried 'Jan 04'!:'Dec
04'! at the beginning of the appropriate terms, instead of
summing the individual months, without success. Is there
a way to shorten the following formula, which Excel will
not accept due to its length, or am I going to have to put
parts of it in different cells? Probably a very basic
simple thing, but self teaching can be soooooo aggravating
with the missing basics one doesn't have.



(SUMPRODUCT(('Jan 04'!$I$2:$I$10000="OC01")*(IF('Jan
Click to expand...
04'$G$2:$G$10000>120, 'Jan 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Feb 04'!$I$2:$I$10000="OC01")*(IF('Feb
04'$G$2:$G$10000>120, 'Feb 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Mar 04'!$I$2:$I$10000="OC01")*(IF('Mar
04'$G$2:$G$10000>120, 'Mar 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('April 04'!$I$2:$I$10000="OC01")*(IF('Apr
04'$G$2:$G$10000>120, 'Apr 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('May 04'!$I$2:$I$10000="OC01")*(IF('May
04'$G$2:$G$10000>120, 'May 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Jun 04'!$I$2:$I$10000="OC01")*(IF('Jun
04'$G$2:$G$10000>120, 'Jun 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Jul 04'!$I$2:$I$10000="OC01")*(IF('Jul
04'$G$2:$G$10000>120, 'Jul 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Aug 04'!$I$2:$I$10000="OC01")*(IF('Aug
04'$G$2:$G$10000>120, 'Aug 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Sep 04'!$I$2:$I$10000="OC01")*(IF('Sep
04'$G$2:$G$10000>120, 'Sep 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Oct 04'!$I$2:$I$10000="OC01")*(IF('Oct
04'$G$2:$G$10000>120, 'Oct 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Nov 04'!$I$2:$I$10000="OC01")*(IF('Nov
04'$G$2:$G$10000>120, 'Nov 04'!$G$2:$G$10000, 0)))
+SUMPRODUCT(('Dec 04'!$I$2:$I$10000="OC01")*(IF('Dec
04'$G$2:$G$10000>120, 'Dec 04'!$G$2:$G$10000, 0))))/
(SUMPRODUCT(('Jan 04'!$I$2:$I$10000="OC01")*(IF('Jan 04'!
$G$2:$G$10000>120, 1, 0)))+SUMPRODUCT(('Feb 04'!
$I$2:$I$10000="OC01")*(IF('Feb 04'!$G$2:$G$10000>120, 1,
0)))+SUMPRODUCT(('Mar 04'!$I$2:$I$10000="OC01")*(IF('Mar
04'!$G$2:$G$10000>120, 1, 0)))+SUMPRODUCT(('Apr 04'!
$I$2:$I$10000="OC01")*(IF('Apr 04'!$G$2:$G$10000>120, 1,
0)))+SUMPRODUCT(('May 04'!$I$2:$I$10000="OC01")*(IF('May
04'!$G$2:$G$10000>120, 1, 0)))+SUMPRODUCT(('Jun 04'!
$I$2:$I$10000="OC01")*(IF('Jun 04'!$G$2:$G$10000>120, 1,
0)))+SUMPRODUCT(('Jul 04'!$I$2:$I$10000="OC01")*(IF('Jul
04'!$G$2:$G$10000>120, 1, 0)))+SUMPRODUCT(('Aug 04'!
$I$2:$I$10000="OC01")*(IF('Aug 04'!$G$2:$G$10000>120, 1,
0)))+SUMPRODUCT(('Sep 04'!$I$2:$I$10000="OC01")*(IF('Sep
04'!$G$2:$G$10000>120, 1, 0)))+SUMPRODUCT(('Oct 04'!
$I$2:$I$10000="OC01")*(IF('Oct 04'!$G$2:$G$10000>120, 1,
0)))+SUMPRODUCT(('Nov 04'!$I$2:$I$10000="OC01")*(IF('Nov
04'!$G$2:$G$10000>120, 1, 0)))+SUMPRODUCT(('Dec 04'!
$I$2:$I$10000="OC01")*(IF('Dec 04'!$G$2:$G$10000>120, 1,
0))))
 
Hi

At start, there wasn't any need for IF(). This will do:
=SUMPRODUCT(('Jan 04'!$I$2:$I$10000="OC01")*('Jan
04'!$G$2:$G$10000>120)*('Jan 04'!$G$2:$G$10000))+SUMPRODUCT(('Feb
04'!$I$2:$I$10000="OC01")*('Feb 04'!$G$2:$G$10000>120)*('Feb
04'!$G$2:$G$10000))+SUMPRODUCT(('Mar 04'!$I$2:$I$10000="OC01")*('Mar
04'!$G$2:$G$10000>120)*('Mar 04'!$G$2:$G$10000))+....

For second, had you named your worksheet without space (Jan04, Feb04, etc.),
you'd spared another 9 characters for every month. Like this
=SUMPRODUCT((Jan04!$I$2:$I$10000="OC01")*(Jan04!$G$2:$G$10000>120)*(Jan04!$G
$2:$G$10000))+SUMPRODUCT((Feb04!$I$2:$I$10000="OC01")*(Feb04!$G$2:$G$10000>1
20)*(Feb04!$G$2:$G$10000))+SUMPRODUCT((Mar04!$I$2:$I$10000="OC01")*(Mar04!$G
$2:$G$10000>120)*(Mar04!$G$2:$G$10000))+....

But you can try with named ranges. Define a named range for every month
JanSum=SUMPRODUCT(('Jan 04'!$I$2:$I$10000="OC01")*('Jan
04'!$G$2:$G$10000>120)*('Jan 04'!$G$2:$G$10000))
FebSum=SUMPRODUCT(('Feb 04'!$I$2:$I$10000="OC01")*('Feb
04'!$G$2:$G$10000>120)*('Feb 04'!$G$2:$G$10000))
MarSum=SUMPRODUCT(('Mar 04'!$I$2:$I$10000="OC01")*('Mar
04'!$G$2:$G$10000>120)*('Mar 04'!$G$2:$G$10000))
.....

Now your formula will be
=JanSum+FebSum+MarSum+....

PS. You can define the named range also so, that the comparision value
("OC01") is read from cell, and the named range adjusts automatically when
copied down. For this select the cell where you want to write the first
formula, and define the named ranges as (I assume the comprision value for
this row is in cell p.e. X2)
JanSum=SUMPRODUCT(('Jan 04'!$I$2:$I$10000=X2)*('Jan
04'!$G$2:$G$10000>120)*('Jan 04'!$G$2:$G$10000))
FebSum=SUMPRODUCT(('Feb 04'!$I$2:$I$10000=X2)*('Feb
04'!$G$2:$G$10000>120)*('Feb 04'!$G$2:$G$10000))
MarSum=SUMPRODUCT(('Mar 04'!$I$2:$I$10000=X2)*('Mar
04'!$G$2:$G$10000>120)*('Mar 04'!$G$2:$G$10000))
etc.
Now, when you copy your formula (sum of months) down, then on second row of
range is returned the sum for condition in X3, etc.
 
Back
Top