SUMPRODUCT formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:F500>C2))

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

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"),--(rangename2>C2)). As I said, that
solved the problem for me.

HTH
 
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?
 
Back
Top