| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can i convert an email address to a web address | Excel Worksheet Functions | |||
| Hyperlink Address and SubAddress not concatenating correctly | Excel Discussion (Misc queries) | |||
| Formular to add to a IP address | Excel Discussion (Misc queries) | |||
| Excel email address hyperlink does not update | Excel Discussion (Misc queries) | |||
| How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
Linear Mode
