Hi,

Could someone please give me some suggestions for how to do the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
....

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple times) on
each row of the first column and then lists all of the States in the second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
....

Any ideas for how to use excel to do what I am trying would be hugely
appreciated.

Thanks,
Michael

Is this data manually entered?

If so, you can still use the suggestion I made at your other post *except*
don't use the in-cell drop down option in data validation.

If someone tries to enter NV for RepNum 20 it'll be rejected.

P.S.

This post contains more info than your other post so I'm not going to follow
the other post anymore.

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

Could someone please give me some suggestions for how to do the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple times)
on
each row of the first column and then lists all of the States in the
second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
...

Any ideas for how to use excel to do what I am trying would be hugely
appreciated.

Thanks,
Michael

Biff,

I just saw your post on this entry now. Thanks for taking the time to help
me.

The data comes from a system generated report that lists all of the sales
for each rep. I downloaded the sales data into excel and now I want to sum up
a reps sales of a particular product in all of his/her states. The problem is
that there are also some entries for certain reps in states that don't belong
to them (which is an indication that the entry is faulty). So, I want to sum
up a reps sales only if those sales were in a state that belonged to them. If
I can create a column that puts a marker in every row that has a state that
belongs to the rep then I could do a sumif function in another column to get
a total of only the sales that are not faulty. The part that I'm having a lot
of trouble with is how to make a marker that identifies whether the state
belongs to the rep or not.

Again, thanks so much for helping me.

Michael

"T. Valko" wrote:

Is this data manually entered?

If so, you can still use the suggestion I made at your other post *except*
don't use the in-cell drop down option in data validation.

If someone tries to enter NV for RepNum 20 it'll be rejected.

P.S.

This post contains more info than your other post so I'm not going to follow
the other post anymore.

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

Could someone please give me some suggestions for how to do the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple times)
on
each row of the first column and then lists all of the States in the
second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
...

Any ideas for how to use excel to do what I am trying would be hugely
appreciated.

Thanks,
Michael

Ok, you can use another column with a helper ("marker") formula.

........A................B.............C.....
1..RepNum Product State
2.. 20 XYZ NM
3.. 20 XYZ AZ
4.. 20 XYZ NV

..........X..............Y.....
1..RepNum STATE
2.. 20 AZ
3.. 20 NM
4.. 30 NY
5.. 30 NJ

Enter this formula in D2 and copy down as needed:

=IF(COUNT(MATCH(1,INDEX((X$2:X$5=A2)*(Y$2:Y$5=C2), 0),0)),"x","")

This will mark valid reps and states with the "x". Then you can sum based on
the marker column = x


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Biff,

I just saw your post on this entry now. Thanks for taking the time to help
me.

The data comes from a system generated report that lists all of the sales
for each rep. I downloaded the sales data into excel and now I want to sum
up
a reps sales of a particular product in all of his/her states. The problem
is
that there are also some entries for certain reps in states that don't
belong
to them (which is an indication that the entry is faulty). So, I want to
sum
up a reps sales only if those sales were in a state that belonged to them.
If
I can create a column that puts a marker in every row that has a state
that
belongs to the rep then I could do a sumif function in another column to
get
a total of only the sales that are not faulty. The part that I'm having a
lot
of trouble with is how to make a marker that identifies whether the state
belongs to the rep or not.

Again, thanks so much for helping me.

Michael

"T. Valko" wrote:

Is this data manually entered?

If so, you can still use the suggestion I made at your other post
*except*
don't use the in-cell drop down option in data validation.

If someone tries to enter NV for RepNum 20 it'll be rejected.

P.S.

This post contains more info than your other post so I'm not going to
follow
the other post anymore.

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

Could someone please give me some suggestions for how to do the
following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12)
Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 ....
$100
20 XYZ NV $150 $0 ....
$0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so
I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple
times)
on
each row of the first column and then lists all of the States in the
second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
...

Any ideas for how to use excel to do what I am trying would be hugely
appreciated.

Thanks,
Michael

Biff,

Thank you for all of your help - I am very grateful for all the time that
you put in to figuring out a solution to this problem. If it weren't for you,
I would have spent hours manually checking for faulty entries.

Have a great weekend!
Michael


"T. Valko" wrote:

Ok, you can use another column with a helper ("marker") formula.

........A................B.............C.....
1..RepNum Product State
2.. 20 XYZ NM
3.. 20 XYZ AZ
4.. 20 XYZ NV

..........X..............Y.....
1..RepNum STATE
2.. 20 AZ
3.. 20 NM
4.. 30 NY
5.. 30 NJ

Enter this formula in D2 and copy down as needed:

=IF(COUNT(MATCH(1,INDEX((X$2:X$5=A2)*(Y$2:Y$5=C2), 0),0)),"x","")

This will mark valid reps and states with the "x". Then you can sum based on
the marker column = x


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Biff,

I just saw your post on this entry now. Thanks for taking the time to help
me.

The data comes from a system generated report that lists all of the sales
for each rep. I downloaded the sales data into excel and now I want to sum
up
a reps sales of a particular product in all of his/her states. The problem
is
that there are also some entries for certain reps in states that don't
belong
to them (which is an indication that the entry is faulty). So, I want to
sum
up a reps sales only if those sales were in a state that belonged to them.
If
I can create a column that puts a marker in every row that has a state
that
belongs to the rep then I could do a sumif function in another column to
get
a total of only the sales that are not faulty. The part that I'm having a
lot
of trouble with is how to make a marker that identifies whether the state
belongs to the rep or not.

Again, thanks so much for helping me.

Michael

"T. Valko" wrote:

Is this data manually entered?

If so, you can still use the suggestion I made at your other post
*except*
don't use the in-cell drop down option in data validation.

If someone tries to enter NV for RepNum 20 it'll be rejected.

P.S.

This post contains more info than your other post so I'm not going to
follow
the other post anymore.

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

Could someone please give me some suggestions for how to do the
following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12)
Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 ....
$100
20 XYZ NV $150 $0 ....
$0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so
I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple
times)
on
each row of the first column and then lists all of the States in the
second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
...

Any ideas for how to use excel to do what I am trying would be hugely
appreciated.

Thanks,
Michael

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Biff,

Thank you for all of your help - I am very grateful for all the time that
you put in to figuring out a solution to this problem. If it weren't for
you,
I would have spent hours manually checking for faulty entries.

Have a great weekend!
Michael


"T. Valko" wrote:

Ok, you can use another column with a helper ("marker") formula.

........A................B.............C.....
1..RepNum Product State
2.. 20 XYZ NM
3.. 20 XYZ AZ
4.. 20 XYZ NV

..........X..............Y.....
1..RepNum STATE
2.. 20 AZ
3.. 20 NM
4.. 30 NY
5.. 30 NJ

Enter this formula in D2 and copy down as needed:

=IF(COUNT(MATCH(1,INDEX((X$2:X$5=A2)*(Y$2:Y$5=C2), 0),0)),"x","")

This will mark valid reps and states with the "x". Then you can sum based
on
the marker column = x


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Biff,

I just saw your post on this entry now. Thanks for taking the time to
help
me.

The data comes from a system generated report that lists all of the
sales
for each rep. I downloaded the sales data into excel and now I want to
sum
up
a reps sales of a particular product in all of his/her states. The
problem
is
that there are also some entries for certain reps in states that don't
belong
to them (which is an indication that the entry is faulty). So, I want
to
sum
up a reps sales only if those sales were in a state that belonged to
them.
If
I can create a column that puts a marker in every row that has a state
that
belongs to the rep then I could do a sumif function in another column
to
get
a total of only the sales that are not faulty. The part that I'm having
a
lot
of trouble with is how to make a marker that identifies whether the
state
belongs to the rep or not.

Again, thanks so much for helping me.

Michael

"T. Valko" wrote:

Is this data manually entered?

If so, you can still use the suggestion I made at your other post
*except*
don't use the in-cell drop down option in data validation.

If someone tries to enter NV for RepNum 20 it'll be rejected.

P.S.

This post contains more info than your other post so I'm not going to
follow
the other post anymore.

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

Could someone please give me some suggestions for how to do the
following:

I have a table that summarizes all of my past sales data for each of
my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12)
Total
20 XYZ NM $150 $700 ....
$50
Sum(Months)
20 XYZ AZ $200 $900 ....
$100
20 XYZ NV $150 $0 ....
$0
...

The problem is that there are some entries that were mistakes
because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and
so
I
want the total column to show $0 and not $150.

I have a table named repStates that lists the Rep Number (multiple
times)
on
each row of the first column and then lists all of the States in the
second
column (one to each row). The table looks like this:

RepNum STATE
20 AZ
20 NM
30 NY
30 NJ
...

Any ideas for how to use excel to do what I am trying would be
hugely
appreciated.

Thanks,
Michael