sumproduct-can fill down change column reference?
 

I have a worksheet that is using the sumproduct function based on
criteria on another sheet. I need to use the same function in other
cells so I am using the fill handle to bring it down, but I want one of
the criteria references to change by column, not by row.

Please help!! :confused:


--
dcd123
------------------------------------------------------------------------
dcd123's Profile: http://www.excelforum.com/member.php...o&userid=25396
View this thread: http://www.excelforum.com/showthread...hreadid=469277

What's your formula look like?

Biff

"dcd123" wrote in
message ...

I have a worksheet that is using the sumproduct function based on
criteria on another sheet. I need to use the same function in other
cells so I am using the fill handle to bring it down, but I want one of
the criteria references to change by column, not by row.

Please help!! :confused:


--
dcd123
------------------------------------------------------------------------
dcd123's Profile:
http://www.excelforum.com/member.php...o&userid=25396
View this thread: http://www.excelforum.com/showthread...hreadid=469277

Can you post your formula? Which part of the formula should be changed
by column as you drag it down?


dcd123 Wrote:
I have a worksheet that is using the sumproduct function based on
criteria on another sheet. I need to use the same function in other
cells so I am using the fill handle to bring it down, but I want one of
the criteria references to change by column, not by row.

Please help!! :confused:


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=469277

=SUMPRODUCT(('Sales thru 9-16-05'!$1:$1=Summary!$B2)*('Sales thru
9-16-05'!B$2:B$1433=TRUE))

While filling down, I want the reference in the second criteria to
change from B$2:B$1433 to C$2:C$1433, then in the next cell down
D$2:D$1433, etc.

Does that help?


--
dcd123
------------------------------------------------------------------------
dcd123's Profile: http://www.excelforum.com/member.php...o&userid=25396
View this thread: http://www.excelforum.com/showthread...hreadid=469277

See attachment, hope it helps.


dcd123 Wrote:
=SUMPRODUCT(('Sales thru 9-16-05'!$1:$1=Summary!$B2)*('Sales thru
9-16-05'!B$2:B$1433=TRUE))

While filling down, I want the reference in the second criteria to
change from B$2:B$1433 to C$2:C$1433, then in the next cell down
D$2:D$1433, etc.

Does that help?


+-------------------------------------------------------------------+
|Filename: Column.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3843 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=469277

Hi!

Based on what your formula is actually doing, try this:

=COUNTIF('sales thru 9-16-05'!$1:$1,Summary!$B2)*COUNTIF(OFFSET('sales thru
9-16-05'!B$2:B$1433,,(ROW(1:1)-1)*1),TRUE)

Biff

"dcd123" wrote in
message ...

=SUMPRODUCT(('Sales thru 9-16-05'!$1:$1=Summary!$B2)*('Sales thru
9-16-05'!B$2:B$1433=TRUE))

While filling down, I want the reference in the second criteria to
change from B$2:B$1433 to C$2:C$1433, then in the next cell down
D$2:D$1433, etc.

Does that help?


--
dcd123
------------------------------------------------------------------------
dcd123's Profile:
http://www.excelforum.com/member.php...o&userid=25396
View this thread: http://www.excelforum.com/showthread...hreadid=469277

Thank you! You saved the day!


--
dcd123
------------------------------------------------------------------------
dcd123's Profile: http://www.excelforum.com/member.php...o&userid=25396
View this thread: http://www.excelforum.com/showthread...hreadid=469277