I have a workbook that has many sheets, the first sheet has a lot of data on it that I want to have in a SUMPRODUCT formula, and I want to be able to copy that formula into multiple cells of the other sheets without changing the range the formula refers to. Example:
=SUMPRODUCT(--('All History'!$B2:$B2000=1),--('All History'!$C2:$C2000=2))

This formula is in one cell, but I want to have it copied to multiple others. But, when I try to copy it, the range changes, i.e. $B2 will become $B3 and so on.
I also need to be able to change the value I'm looking for without changing the rest of the formula, i.e. change the =1 value to =2, and so on. Any ideas?

Use

$B$2:$B$2000


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"frankjh19701" wrote in message
...

I have a workbook that has many sheets, the first sheet has a lot of
data on it that I want to have in a SUMPRODUCT formula, and I want to
be able to copy that formula into multiple cells of the other sheets
without changing the range the formula refers to. Example:
=SUMPRODUCT(--('All History'!$B2:$B2000=1),--('All
History'!$C2:$C2000=2))

This formula is in one cell, but I want to have it copied to
multiple others. But, when I try to copy it, the range changes, i.e.
$B2 will become $B3 and so on.
I also need to be able to change the value I'm looking for without
changing the rest of the formula, i.e. change the =1 value to =2, and
so on. Any ideas?




--
frankjh19701

For the second part of your question you could use a helper
column (say column G) with the numbers 1 to 20 or whatever
descending down the column.

Your formula will then be
=SUMPRODUCT(--('All History'!$B$2:$B$2000=$G1)

As you copy down the $G1 reference will update to $G2, $G3 etc.

HTH
Martin

Thank you for the help, however, it doesn't completely work. I totally forgot about using the $ to state that I want the constant in a formula, but I still can't copy the formula without A)Somehow altering the original formula and B)the formula doesn't stay the same or work after pasting it into a different cell. Let me explain:
I have a formula that I've pieced together that is :
=SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3),--(All!$C$2:$C$2000=All!$M4))+SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3)--(All!$D$2:$D$2000=All!$M4))+SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3),--(All!$E$2:$E$2000=All!$M4))+SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3),--(All!$F$2:$F$2000=All!$M4))+SUMPRODUCT(--(All!$C$2:$C$2000=All!$M3)--(All!$D$2:$D$2000=All!$M4))+SUMPRODUCT(--(All!$C$2:$C$2000=All!$M3),--(All!$E$2:$E$2000=All!$M4))+SUMPRODUCT(--(All!$C$2:$C$2000=All!$M3),--(All!$F2:$F$2000=All!$M4))+SUMPRODUCT(--(All!$D$2:$D$2000=All!$M3)--(All!$E$2:$E$2000=All!$M4))+SUMPRODUCT(--(All!$D$2:$D$2000=All!$M3),--(All!$F2:$F$2000=All!$M4))+SUMPRODUCT(--(All!$E$2:$E$2000=All!$M3)--(All!$F$2:$F$2000=All!$M4))

"All" has been changed from "All History" due to the length of the formula (I couldn't complete the formula without getting an error message about it being too long). And I have been analyzing data and comparing it to repeating sequences across columns (But, in the same row). Hence why I have the formula refering to Column B to Column C, then Column B to Column D, and so forth.

Now, when I try to copy the formula, it alters the formula and what value the cell reflects is #######. Even if all I do is click on the cell and then just click in the formula bar (Not changing/typing anything) just clicking in the formula bar!

I took the advice of making a "Helper Column", which is in the sheet All and in the column M, but the rest of this is all a mystery to me. How can the formula change when I'm not doing anything to it but clicking in the formula bar? And how can I copy this formula into other cells without changing it? I really need some help on this.

Thank you for the help, but I now have a new problem: the helper column seems to stop "helping" after the first dozen numbers or so, Why is this?
The first couple of lines work on the first few sheets in the workbook, but then it seems to stop - all of my formulas only produce zeros. It shouldn't produce zeros especially when there is data that I know for a fact should be there. What am I doing wrong? I have a workbook with aprox. 50 pages and they all reference the first in their formulas. If you need more info, please let me know. Since there is SO much data and it is sensitive, I don't want to try to post it here. I look forward to your help.