Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I automatically update column index number in VLookup whe. | Excel Worksheet Functions | |||
Sumproduct - multiple criteria in Column A | Excel Worksheet Functions | |||
Naming column in Index Function | Excel Worksheet Functions | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
Column Index | Excel Worksheet Functions |