Formula Question

[DBaker7777]

I have an excel sheet the retrieves data from a Sharepoint site. When the data is refreshed and new data populated the target range in my formula updates and the result I get is N/A. The formula I have is =Sumproduct((Results!$A$2:$A$500=C2)*(Results!$G$2:$P$500<>"")). Like I said as soon as a new row of data populates in the Results tab the formula the front part of the formula changes to $A$2:$A$501. How can I prevent that?

 

[SyedUsman]

Try this

=SUMPRODUCT((INDIRECT("A$2:$A$500")=C2)*(INDIRECT("$G$2:$P$500")<>""))

 

[DBaker7777]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Try this

=SUMPRODUCT((INDIRECT("A$2:$A$500")=C2)*(INDIRECT("$G$2:$P$500")<>""))

The formula worked when I tested it. How would I reference another sheet in this formula? The sheet I am pulling the data from is on a tab called Results. Thank you for your help

 

[DBaker7777]

Never mind I think I got it. Thank you again. I typed the name of the sheet in another cell and got this formula:

=SUMPRODUCT((INDIRECT(J329&"!"&"A$2:$A$500")=C317)*(INDIRECT(J329&"!"&"$G$2:$P$500")<>""))