sumproduct column index

[MHoffmeier]

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

 

[Guest]

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]

I think I figured out how to do this with =offset. I will post solution
when I get back to work

 

[MHoffmeier]

This is what worked
=SUMPRODUCT(--('Interior Completion Status'!$A$6:$A$149=A13)*--('Interior
Completion Status'!$B$6:$B$149=B13)*--(OFFSET('Interior Completion
Status'!$F$5,1,2*C13,144,1)))

It took me an embarrassingly long period of time to realize that the height
was 144, not 143 (149-6), and that the origin was f5, not f6 when using the
value 1 for offset..