Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!
=SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000)
returns #Value! both =(INDIRECT(ADDRESS(ROW(),2))) =(INDIRECT(ADDRESS(2,2))) alone returns the lookup value from b2:b1000 that i am searching for. =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(2,2)))),T2:T1000) returns what i need =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(2,2)))),T2:T1000) & " " & ROW() returns what i need + 2 which is the row im looking to acquire in this example I need row() or something similar to work because this function will need to be on every line between 2 and 1000 with the corrosponding row. any help is appreciated. thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!
Try
=SUMPRODUCT(--(B2:B1000=N(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000) Regards, Peo Sjoblom "Brian Williams" <Brian wrote in message ... =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000) returns #Value! both =(INDIRECT(ADDRESS(ROW(),2))) =(INDIRECT(ADDRESS(2,2))) alone returns the lookup value from b2:b1000 that i am searching for. =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(2,2)))),T2:T1000) returns what i need =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(2,2)))),T2:T1000) & " " & ROW() returns what i need + 2 which is the row im looking to acquire in this example I need row() or something similar to work because this function will need to be on every line between 2 and 1000 with the corrosponding row. any help is appreciated. thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!
"Peo Sjoblom" wrote...
Try =SUMPRODUCT(--(B2:B1000=N(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000) .... "Brian Williams" wrote... =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000) returns #Value! both =(INDIRECT(ADDRESS(ROW(),2))) =(INDIRECT(ADDRESS(2,2))) alone returns the lookup value from b2:b1000 that i am searching for. Never any need to use INDIRECT(ADDRESS(..)). To date, no one has posted anything that couldn't be done more simply using INDEX, OFFSET or INDIRECT alone with R1C1 address syntax. Looks like this would be the formula in some cell in row 2. You could use =SUMPRODUCT(--(B2:B1000=INDEX(2:2,2)),T2:T1000) or if you simply must use INDIRECT, =SUMPRODUCT(--(B2:B1000=INDIRECT("RC2",0)),T2:T1000) Both formulas are immune to column insertion or deletion. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!
"Harlan Grove" wrote...
.... =SUMPRODUCT(--(B2:B1000=INDEX(2:2,2)),T2:T1000) .... Hate it when I notice something just after posting. Why not just use =SUMIF(T2:T1000,B2) ? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!
"Harlan Grove" wrote...
... =SUMIF(T2:T1000,B2) Ugh! Make that =SUMIF(B2:B1000,B2,T2:T1000) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE
I have multiple conditions i need to satisfy. I took the formula done to the
basic form for troubleshooting, but still need to satisfy 3 conditions in the future. I need the formula to be dynamic also because I will be getting the information from the database. INDEX didnt work for me. "Harlan Grove" wrote: "Harlan Grove" wrote... ... =SUMIF(T2:T1000,B2) Ugh! Make that =SUMIF(B2:B1000,B2,T2:T1000) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!
=SUMPRODUCT(--($B$2 B$6000=INDEX(INDIRECT(ADDRESS(ROW(),2)),1)),$T$2 T$6000)
& " " & ROW() bam!!! during evaluate formula i noticed INDIRECT(ADDRESS(ROW(),2)) was returning an array that had brackets { } INDIRECT(ADDRESS(2,2)) was returning a value thanks to both of you for your help. "Brian Williams" wrote: =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000) returns #Value! both =(INDIRECT(ADDRESS(ROW(),2))) =(INDIRECT(ADDRESS(2,2))) alone returns the lookup value from b2:b1000 that i am searching for. =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(2,2)))),T2:T1000) returns what i need =SUMPRODUCT(--(B2:B1000=(INDIRECT(ADDRESS(2,2)))),T2:T1000) & " " & ROW() returns what i need + 2 which is the row im looking to acquire in this example I need row() or something similar to work because this function will need to be on every line between 2 and 1000 with the corrosponding row. any help is appreciated. thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Returning Multiple Text or Address Locations | Excel Worksheet Functions | |||
INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions | |||
Indirect and Address in Reference to other sheets | Excel Worksheet Functions | |||
Summing a range using INDIRECT & ADDRESS | Excel Worksheet Functions |