SUMPRODUCT vs Database Query
 

Good Morning,

I am using SUMPRODUCT to return the sum of a selection from a spreadsheet
that it downloaded from an access database.

However everytime I refresh my database query, my SUMPRODUCT formula changes:

FROM: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1000='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

TO: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1005='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

As a result instead of getting values on my summary page I get "N/A".

Is there a way to prevent the formulas from changing everytime I refresh my
database?

Best Regards,

Vanessa Simmonds

SUMPRODUCT vs Database Query
 

I'm not sure that your original "From" sumproduct indicated works in the
first place? Don't think sumproduct supports the evaluation of the uneven
arrays that it contains.

Btw, I've responded since in your other thread,
http://tinyurl.com/kowvsk
The play should return the required results.
Appreciate it if you could close off the discussions there.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Vanessa Simmonds" wrote:
Good Morning,

I am using SUMPRODUCT to return the sum of a selection from a spreadsheet
that it downloaded from an access database.

However everytime I refresh my database query, my SUMPRODUCT formula changes:

FROM: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1000='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

TO: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1005='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

As a result instead of getting values on my summary page I get "N/A".

Is there a way to prevent the formulas from changing everytime I refresh my
database?

Best Regards,

Vanessa Simmonds

SUMPRODUCT vs Database Query
 

Good Morning, Max.

Trust me, it does work. But my only problem is when I refresh my query.

Do you have any suggestion?

Thank you very much,

Vanessa

"Max" wrote:

I'm not sure that your original "From" sumproduct indicated works in the
first place? Don't think sumproduct supports the evaluation of the uneven
arrays that it contains.

Btw, I've responded since in your other thread,
http://tinyurl.com/kowvsk
The play should return the required results.
Appreciate it if you could close off the discussions there.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Vanessa Simmonds" wrote:
Good Morning,

I am using SUMPRODUCT to return the sum of a selection from a spreadsheet
that it downloaded from an access database.

However everytime I refresh my database query, my SUMPRODUCT formula changes:

FROM: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1000='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

TO: (SUMPRODUCT(('SU Shipments'!$E$1:$AA$1='Daily Shipments'!C$7)*('SU
Shipments'!$D$2:$D$1005='Daily Shipments'!$B9)*('SU
Shipments'!$E$2:$AA$1000)))/1000

As a result instead of getting values on my summary page I get "N/A".

Is there a way to prevent the formulas from changing everytime I refresh my
database?

Best Regards,

Vanessa Simmonds

SUMPRODUCT vs Database Query
 

Do you have any suggestion?
Nein, I'm out of further comments here. You might want to re-surface it to
others' radar via a new thread. Poor me still struggling to understand how
your original sumproduct expression works as-is.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---