SUMPRODUCT formula
 

So I have a sheet that gaves external data from OBDC. I am using a lot of
SUMPRODUCT formulas.

Everytime I update the query all the formulas ranges seem to change.
IE:
I have set a formula of:
=SUMPRODUCT(--('Wpg #s data'!C6:C500="Incident"),--('Wpg #s data'!F6:F500C2))

And it changes to:
=SUMPRODUCT(--('Wpg #s data'!C6:C415="Incident"),--('Wpg #s data'!F6:F415C2))

Why is that happening? I will usually have a range of around 300 but I am
concerned that if it changes it on the fly that there could be issues with
the collection of data and therefore the report could be wrong. Any thoughts?

I have that exact situation and solved it using dynamic range names. That
would change your formula to
=SUMPRODUCT(--(rangename1="Incident"),--(rangename2C2)). As I said, that
solved the problem for me.

HTH

"shmurphing" wrote:

So I have a sheet that gaves external data from OBDC. I am using a lot of
SUMPRODUCT formulas.

Everytime I update the query all the formulas ranges seem to change.
IE:
I have set a formula of:
=SUMPRODUCT(--('Wpg #s data'!C6:C500="Incident"),--('Wpg #s data'!F6:F500C2))

And it changes to:
=SUMPRODUCT(--('Wpg #s data'!C6:C415="Incident"),--('Wpg #s data'!F6:F415C2))

Why is that happening? I will usually have a range of around 300 but I am
concerned that if it changes it on the fly that there could be issues with
the collection of data and therefore the report could be wrong. Any thoughts?

OK. Just a little confused. So how does the formula know that rangname1=
column C? Or does it look at the entire sheet for the word incident? What
if that word appears in other columns -- either by itself or within a phrase?



"JBoulton" wrote:

I have that exact situation and solved it using dynamic range names. That
would change your formula to
=SUMPRODUCT(--(rangename1="Incident"),--(rangename2C2)). As I said, that
solved the problem for me.

HTH

"shmurphing" wrote:

So I have a sheet that gaves external data from OBDC. I am using a lot of
SUMPRODUCT formulas.

Everytime I update the query all the formulas ranges seem to change.
IE:
I have set a formula of:
=SUMPRODUCT(--('Wpg #s data'!C6:C500="Incident"),--('Wpg #s data'!F6:F500C2))

And it changes to:
=SUMPRODUCT(--('Wpg #s data'!C6:C415="Incident"),--('Wpg #s data'!F6:F415C2))

Why is that happening? I will usually have a range of around 300 but I am
concerned that if it changes it on the fly that there could be issues with
the collection of data and therefore the report could be wrong. Any thoughts?

If you are unfamiliar with dynamic named ranges:
http://www.cpearson.com/excel/named.htm
Scroll down a bit to find the section about Dynamic Named Ranges.

tj

"JBoulton" wrote:

I have that exact situation and solved it using dynamic range names. That
would change your formula to
=SUMPRODUCT(--(rangename1="Incident"),--(rangename2C2)). As I said, that
solved the problem for me.

HTH

"shmurphing" wrote:

So I have a sheet that gaves external data from OBDC. I am using a lot of
SUMPRODUCT formulas.

Everytime I update the query all the formulas ranges seem to change.
IE:
I have set a formula of:
=SUMPRODUCT(--('Wpg #s data'!C6:C500="Incident"),--('Wpg #s data'!F6:F500C2))

And it changes to:
=SUMPRODUCT(--('Wpg #s data'!C6:C415="Incident"),--('Wpg #s data'!F6:F415C2))

Why is that happening? I will usually have a range of around 300 but I am
concerned that if it changes it on the fly that there could be issues with
the collection of data and therefore the report could be wrong. Any thoughts?

http://www.contextures.com/xlNames01.html#Dynamic

Regards,

Peo Sjoblom

"shmurphing" wrote:

OK. Just a little confused. So how does the formula know that rangname1=
column C? Or does it look at the entire sheet for the word incident? What
if that word appears in other columns -- either by itself or within a phrase?



"JBoulton" wrote:

I have that exact situation and solved it using dynamic range names. That
would change your formula to
=SUMPRODUCT(--(rangename1="Incident"),--(rangename2C2)). As I said, that
solved the problem for me.

HTH

"shmurphing" wrote:

So I have a sheet that gaves external data from OBDC. I am using a lot of
SUMPRODUCT formulas.

Everytime I update the query all the formulas ranges seem to change.
IE:
I have set a formula of:
=SUMPRODUCT(--('Wpg #s data'!C6:C500="Incident"),--('Wpg #s data'!F6:F500C2))

And it changes to:
=SUMPRODUCT(--('Wpg #s data'!C6:C415="Incident"),--('Wpg #s data'!F6:F415C2))

Why is that happening? I will usually have a range of around 300 but I am
concerned that if it changes it on the fly that there could be issues with
the collection of data and therefore the report could be wrong. Any thoughts?