sumif with nested address
 

I need help creating a sumif formula with a range that changes. I think I
can use a nested address function, but the formula results in an error.

Here is what i did, please correct where i went wrong:

I have 2 sheets in a workbook.
Sheet 1 is titled raw, and is set up like this:

Week Product a Product b
200639 1,000 500
200640 900 700

Sheet 2 is my summary with the sumif which isn't working as I need it to work
This formula works
200639
Product a =SUMIF(raw!$A$5:$A$401,C$3,raw!$E$5:$E$401)


This formula doesn't work
200639
Product a
=SUMIF(raw!(ADDRESS(MATCH(C3,raw!A:A,0),1)):$A$401 ,C$3,raw!$E$5:$E$401)
***the result of this formula is $A$5 (ADDRESS(MATCH(C3,raw!A:A,0),1))

How do I get the address function that results in $A$5 to be recogized in
the SumIf formula?
Many thanks for your help!

sumif with nested address
 

try this. you are missing INDIRECT

=SUMIF(INDIRECT("raw!"&ADDRESS(MATCH(C3,raw!A:A,0) ,1)&":$A$401"),C$3,raw!$E$5:$E$401)
--
Allllen


"alison" wrote:

I need help creating a sumif formula with a range that changes. I think I
can use a nested address function, but the formula results in an error.

Here is what i did, please correct where i went wrong:

I have 2 sheets in a workbook.
Sheet 1 is titled raw, and is set up like this:

Week Product a Product b
200639 1,000 500
200640 900 700

Sheet 2 is my summary with the sumif which isn't working as I need it to work
This formula works
200639
Product a =SUMIF(raw!$A$5:$A$401,C$3,raw!$E$5:$E$401)


This formula doesn't work
200639
Product a
=SUMIF(raw!(ADDRESS(MATCH(C3,raw!A:A,0),1)):$A$401 ,C$3,raw!$E$5:$E$401)
***the result of this formula is $A$5 (ADDRESS(MATCH(C3,raw!A:A,0),1))

How do I get the address function that results in $A$5 to be recogized in
the SumIf formula?
Many thanks for your help!

sumif with nested address
 

You have left the reader with lots of questions: what is in C3 etc.
But I think you need to convert the text A5 into a cell reference with
INDIRECT
So although I do not really see what you are doing I suggest you try
=SUMIF(INDIRECT("raw!"&ADDRESS(MATCH(C3,raw!A:A,0) ,1)):$A$401,C$3,raw!$E$5:$E$401)
but I cannot test this
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"alison" wrote in message
...
I need help creating a sumif formula with a range that changes. I think I
can use a nested address function, but the formula results in an error.

Here is what i did, please correct where i went wrong:

I have 2 sheets in a workbook.
Sheet 1 is titled raw, and is set up like this:

Week Product a Product b
200639 1,000 500
200640 900 700

Sheet 2 is my summary with the sumif which isn't working as I need it to
work
This formula works
200639
Product a =SUMIF(raw!$A$5:$A$401,C$3,raw!$E$5:$E$401)


This formula doesn't work
200639
Product a
=SUMIF(raw!(ADDRESS(MATCH(C3,raw!A:A,0),1)):$A$401 ,C$3,raw!$E$5:$E$401)
***the result of this formula is $A$5 (ADDRESS(MATCH(C3,raw!A:A,0),1))

How do I get the address function that results in $A$5 to be recogized in
the SumIf formula?
Many thanks for your help!

sumif with nested address
 

Hi!

Try this:

=SUM(E5:INDEX(E5:E401,MATCH(C3,A5:A401,0)))

Biff

"alison" wrote in message
...
I need help creating a sumif formula with a range that changes. I think I
can use a nested address function, but the formula results in an error.

Here is what i did, please correct where i went wrong:

I have 2 sheets in a workbook.
Sheet 1 is titled raw, and is set up like this:

Week Product a Product b
200639 1,000 500
200640 900 700

Sheet 2 is my summary with the sumif which isn't working as I need it to
work
This formula works
200639
Product a =SUMIF(raw!$A$5:$A$401,C$3,raw!$E$5:$E$401)


This formula doesn't work
200639
Product a
=SUMIF(raw!(ADDRESS(MATCH(C3,raw!A:A,0),1)):$A$401 ,C$3,raw!$E$5:$E$401)
***the result of this formula is $A$5 (ADDRESS(MATCH(C3,raw!A:A,0),1))

How do I get the address function that results in $A$5 to be recogized in
the SumIf formula?
Many thanks for your help!

sumif with nested address
 

Ooops!

I think I have it backwards. Try this:

=SUM(E401:INDEX(E5:E401,MATCH(C3,A5:A401,0)))

Biff

"Biff" wrote in message
...
Hi!

Try this:

=SUM(E5:INDEX(E5:E401,MATCH(C3,A5:A401,0)))

Biff

"alison" wrote in message
...
I need help creating a sumif formula with a range that changes. I think I
can use a nested address function, but the formula results in an error.

Here is what i did, please correct where i went wrong:

I have 2 sheets in a workbook.
Sheet 1 is titled raw, and is set up like this:

Week Product a Product b
200639 1,000 500
200640 900 700

Sheet 2 is my summary with the sumif which isn't working as I need it to
work
This formula works
200639
Product a =SUMIF(raw!$A$5:$A$401,C$3,raw!$E$5:$E$401)


This formula doesn't work
200639
Product a
=SUMIF(raw!(ADDRESS(MATCH(C3,raw!A:A,0),1)):$A$401 ,C$3,raw!$E$5:$E$401)
***the result of this formula is $A$5 (ADDRESS(MATCH(C3,raw!A:A,0),1))

How do I get the address function that results in $A$5 to be recogized in
the SumIf formula?
Many thanks for your help!