Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MHoffmeier
 
Posts: n/a
Default sumproduct column index

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


  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



  #3   Report Post  
MHoffmeier
 
Posts: n/a
Default

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

"MHoffmeier" wrote in message
...
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




  #4   Report Post  
MHoffmeier
 
Posts: n/a
Default

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..

"MHoffmeier" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I automatically update column index number in VLookup whe. Gene Excel Worksheet Functions 2 July 10th 12 11:23 AM
Sumproduct - multiple criteria in Column A briank Excel Worksheet Functions 2 January 6th 05 07:44 PM
Naming column in Index Function mlkpied Excel Worksheet Functions 3 December 7th 04 01:20 AM
vlookup change column index position - without changing formulae loopyloobyloo Excel Worksheet Functions 1 November 26th 04 02:35 PM
Column Index Megan Excel Worksheet Functions 2 November 11th 04 03:09 PM


All times are GMT +1. The time now is 05:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"