Yes, you can use index and refer to the columns, to simplify, if your table
is A1:E500
and is named MyTable and you want to check for for A1:A500=H1 and sum the
correspsonding cells in E1:E
=SUMPRODUCT(--(INDEX(MyTable,,1)=H1),INDEX(MyTable,,5))
now to make it even more flexible replace the indexed numbers (in this case
1 and 5) with cell references where you would put them then you don't have to
edit the formula at all and if you use a named table you won't have to bother
about sheet names etc
Note that your formula can be rewritten as
=SUMPRODUCT(--('Interior Completion Status'!$A$6:$A$149=A132),--('Interior
Completion Status'!$B$6:$B$149=B132),'Interior Completion Status'!$V$6:$V$149)
Regards,
Peo Sjoblom
"MHoffmeier" wrote:
Hello,
In the formula below, I am using two columns, A&B to return numbers from the
Interior Completion Status worksheet in column V. In each row of the active
sheet, I have to change the column in Interior Completion Status that is
being referenced. This means I cannot just fill one typical formula down
the entire sheet. Is it possible to reference a table in the Interior
Completion Status sheet, and use a number in the active sheet to reference
the correct column? I need to use sumproduct due to the multiple variables.
=SUMPRODUCT(--('Interior Completion Status'!$A$6:$A$149=A132)*--('Interior
Completion Status'!$B$6:$B$149=B132)*--('Interior Completion
Status'!$V$6:$V$149))
Thanks
|