H
Hurleymc
I'm having a problem returning a value based on a sumproduct formula
that has multiple conditions.
My master spreadsheet has a column listing identifiers, another column
that has the state of origin, and a third column that has the
destination state. What I want to do is come up with a count of all
trips with a certain identifier, coming from a certain group of
states, going to another certain group of states.
I can get the formula to work for individual state to individual state
but I need to factor in these groups. What I did was list out the
state abbreviations in each group and then assign that data series a
name. I then referenced the name of the group in the formula.
However, all I get is #value! It doesn't like the name of the group
for some reason.
This is my formula:
=sumproduct(--($e2:e$2330="XXXX"),(--($D$2:$D$2330=west),(--($B$2:$B$2330="nv")))
in this case, the E Column holds the 4 digit identifier, the D column
holds the state of origin, and the B column holds the destination
state. I am looking for all trips with this identifier coming from
"west" (which I have defined for the sake of this argument as CA and
WA) going to Nevada.
If I were to change this statement to read:
=sumproduct(--($e2:e$2330="XXXX"),(--($D$2:$D$2330="ca"),(--($B$2:$B$2330="nv")))
I will get number returned to me. If I add in another sumproduct
statement that looks at shipments coming out of Washington, I will get
the correct number but I have some zones with 10 states and I'm going
to run out of room in the formula long before I get all the possible
combinations typed out.
My question is how do I get Excel to recognize that text string of
west to include WA and CA and how do I get it to compare the values in
the D column to the values in "west" and include all matches?
I've been beating my head in all day with this problem so if anyone
can help, thanks a ton.
that has multiple conditions.
My master spreadsheet has a column listing identifiers, another column
that has the state of origin, and a third column that has the
destination state. What I want to do is come up with a count of all
trips with a certain identifier, coming from a certain group of
states, going to another certain group of states.
I can get the formula to work for individual state to individual state
but I need to factor in these groups. What I did was list out the
state abbreviations in each group and then assign that data series a
name. I then referenced the name of the group in the formula.
However, all I get is #value! It doesn't like the name of the group
for some reason.
This is my formula:
=sumproduct(--($e2:e$2330="XXXX"),(--($D$2:$D$2330=west),(--($B$2:$B$2330="nv")))
in this case, the E Column holds the 4 digit identifier, the D column
holds the state of origin, and the B column holds the destination
state. I am looking for all trips with this identifier coming from
"west" (which I have defined for the sake of this argument as CA and
WA) going to Nevada.
If I were to change this statement to read:
=sumproduct(--($e2:e$2330="XXXX"),(--($D$2:$D$2330="ca"),(--($B$2:$B$2330="nv")))
I will get number returned to me. If I add in another sumproduct
statement that looks at shipments coming out of Washington, I will get
the correct number but I have some zones with 10 states and I'm going
to run out of room in the formula long before I get all the possible
combinations typed out.
My question is how do I get Excel to recognize that text string of
west to include WA and CA and how do I get it to compare the values in
the D column to the values in "west" and include all matches?
I've been beating my head in all day with this problem so if anyone
can help, thanks a ton.