# Fix columns for sumproduct

Dougal Ross
Guest
Posts: n/a

 15th Mar 2012
Hi,
I have 2 formulas I am using to look for conditions in my data.
One to find when one of two criteria is met:
=SUMPRODUCT((V4:V20>1.05)+(X4:X20>8.05))

and one to find when BOTH criteria are met:
=SUMPRODUCT(--(V4:V20>1.05),--(X4:X20>8.05))

I am regularly adding to the data and I only want these formulas to
always work for the last 16 rows only (rows 4-20). At the moment
however if I insert a row the formulas change from row 20 to 21. How
do I fix it at 20 so when I insert a new row it doesn't change?

Thanks

Dougal Ross
Guest
Posts: n/a

 15th Mar 2012
Sorry, the headng should say "Fix cells" not columns.

joeu2004
Guest
Posts: n/a

 15th Mar 2012
"Dougal Ross" <(E-Mail Removed)> wrote:
> I have 2 formulas I am using to look for conditions in my data.
> One to find when one of two criteria is met:
> =SUMPRODUCT((V4:V20>1.05)+(X4:X20>8.05))
>
> and one to find when BOTH criteria are met:
> =SUMPRODUCT(--(V4:V20>1.05),--(X4:X20>8.05))
>
> I am regularly adding to the data and I only want these formulas to
> always work for the last 16 rows only (rows 4-20). At the moment
> however if I insert a row the formulas change from row 20 to 21.
> How do I fix it at 20 so when I insert a new row it doesn't change?

I'm confused. If you "want these formulas to always work for the last 16
rows only", don't you want V4:V20 and X4:X20 to change to V5:V21 and X5:X21
when you add one new row, for example, instead of "fix[ing] it at 20"?

(If you want to "fix it at 20", you might use INDIRECT("V4:V20") for
example. Otherwise....)

Assuming you want V5:V21 and X5:X21, and assuming that column V and X always
have the same number of lines of data, I would suggest that you put the
following formula into some cell, e.g. Z1:

=MATCH(1E300,V:V)

If you know that the data will never go beyond row 10000, say, it would be
better to write:

=MATCH(1E300,V1:V10000)

The MATCH formulas produce the row number of the last number in column V
(and X presumably). 1E300 is presumably larger than any expected number in
volumn V.

Then, your SUMPRODUCT formulas can be (with some other unrelated corrections
and improvements):

=SUMPRODUCT(--((INDEX(V:V,Z1-19):INDEX(V:V,Z1)>1.05)
+(INDEX(X:X,Z1-19):INDEX(X:X,Z1)>8.05)>0))

=SUMPRODUCT((INDEX(V:V,Z1-19):INDEX(V:V,Z1)>1.05)
*(INDEX(X:X,Z1-19):INDEX(X:X,Z1)>8.05))

Note the important correction in the first formula. Your intention is to
count the number of rows when V>1.05 or X>8.05. If both conditions are
true, your original formula would count as 2. I presume you want to count
as 1. Right?

Also note the simplication(?) in the second formula. It is certainly easier
to write. It might also be more efficient.

Finally, if you are using XL2007 or later, you can replace the last
SUMPRODUCT with the following:

=COUNTIFS(INDEX(V:V,Z1-19):INDEX(V:V,Z1),">1.05",
INDEX(X:X,Z1-19):INDEX(X:X,Z1),">8.05")

FYI, I use the form INDEX:INDEX instead of OFFSET(V:V,Z1-19,0,20,1), for
example, because OFFSET is a volatile function, whereas INDEX is not.
Formulas that use OFFSET are recalculated every time any cell in any
worksheet in the workbook is edited. Not so bad if you have only a few such
formulas. It can slow thing down significantly if you have thousands of
such formulas.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 23rd Nov 2005 03:36 PM George Hester Microsoft Windows 2000 1 24th Jun 2005 09:15 PM Rick Windows XP Basics 4 30th Jun 2004 07:56 PM

Features