Sumproduct #N/A! error issue

[A. Gallardo]

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)*(BBDDTareas!$H$2:$H$5041

EstadisticasGenerales!E3)*(BBDDTareas!$F$2:$F$5041

<EstadisticasGenerales!E3))+SUMAPRODUCTO((BBDDTareas!$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

 

[Arvi Laanemets]

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,--(Range2>EstadisticasGenerales!E3),--(Range1<EstadisticasGenerales!E3))+SUMPRODUCT(Range3,--(Range1<EstadisticasGenerales!E3))--Arvi Laanemets( My real mail address: arvil<at>tarkon.ee )"A. Gallardo" <[email protected]> wrote in messagehave 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)*(BBDDTareas!$H$2:$H$5041>>EstadisticasGenerales!E3)*(BBDDTareas!$F$2:$F$5041> <EstadisticasGenerales!E3))+SUMAPRODUCTO((BBDDTareas!$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>

 

[A. Gallardo]

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

A. Gallardo