=(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE!

[Guest]

=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.

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(B2:B1000=N(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000)


Regards,

Peo Sjoblom

 

[Harlan Grove]

Try

=SUMPRODUCT(--(B2:B1000=N(INDIRECT(ADDRESS(ROW(),2)))),T2:T1000) ....
"Brian Williams" wrote...

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.

 

[Harlan Grove]

=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)

?

 

[Harlan Grove]

...
=SUMIF(T2:T1000,B2)

Ugh! Make that

=SUMIF(B2:B1000,B2,T2:T1000)

 

[Guest]

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.

 

[Guest]

This returns 0 instead of an error, and doesnt return the correct value when
i use
N(INDIRECT(ADDRESS(2,2))) returns 0
INDIRECT(ADDRESS(2,2)) return the correct data.

Brian

 

[Guest]

=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.