I have not been able to find the answer after a while searching for it
so here is my question:

I am using the sumproduct function for calculating subtotals in 2
corporate worksheets dealing with production data and with the
Information Systems user requests database. The two sheets are working
fine. One of them imports data from sql server and the other from
access.

My problem is that when I refresh the data the references of the
sumproduct formule change but not all. Here is an example:

=SUMPRODUCT((BBDDTareas!$AA$2:$AA$5000)*(BBDDTarea s!$H$2:$H$5041
EstadisticasGenerales!E3)*(BBDDTareas!$F$2:$F$504 1
<EstadisticasGenerales!E3))+SUMAPRODUCTO((BBDDTare as!$AB$2:$AB$5000)
*(BBDDTareas!$F$2:$F$5041<EstadisticasGenerales!E3 ))

As you see, the parts with the condition change range (in this case
from rows range 2:5000 prior to the data update to range 2:5041) but
not in the part with the data that actually sum data (the first one).
This leads to an #N/A! error.

I guess this is a really basic issue but I cannot find how to get rid
of it. Thank in advance for anyone that can help.

A. Gallardo

Hi

Define ranges on sheet BBDDTareas as dynamic ones - with same column as
conditional one for all of them. Like
Range1=BBDDTareas!$F$1,1,,COUNTA(BBDDTareas!$F:$F)-1,1)
Range2=BBDDTareas!$H$1,1,,COUNTA(BBDDTareas!$F:$F)-1,1)
Range3=BBDDTareas!$AB$1,1,,COUNTA(BBDDTareas!$F:$F )-1,1)

Now your formula will be like
=SUMPRODUCT(Range3,--(Range2EstadisticasGenerales!E3),--(Range1<EstadisticasGenerales!E3))+SUMPRODUCT(Rang e3,--(Range1<EstadisticasGenerales!E3))--Arvi Laanemets( My real mail address: arvil<attarkon.ee )"A. Gallardo" wrote in oglegroups.com...I have not been able to find the answer after a while searching for it so here is my question: I am using the sumproduct function for calculating subtotals in 2 corporate worksheets dealing with production data and with the Information Systems user requests database. The two sheets are working fine. One of them imports data from sql server and the other from access. My problem is that when I refresh the data the references of the sumproduct formule change but not all. Here is an example: =SUMPRODUCT((BBDDTareas!$AA$2:$AA$5000)*(BBDDTarea s!$H$2:$H$5041EstadisticasGenerales!E3)*(BBDDTar eas!$F$2:$F$5041 <EstadisticasGenerales!E3))+SUMAPRODUCTO((BBDDTare as!$AB$2:$AB$5000) *(BBDDTareas!$F$2:$F$5041<EstadisticasGenerales!E3 )) As you see, the parts with the condition change range (in this case from rows range 2:5000 prior to the data update to range 2:5041) but not in the part with the data that actually sum data (the first one). This leads to an #N/A! error. I guess this is a really basic issue but I cannot find how to get rid of it. Thank in advance for anyone that can help. A. Gallardo

Thanks a lot. After some test and fix, I have the sheet finally working
properly. Your solution works fine.

A. Gallardo