#N/A if cell contents not part of an array

[MichaelR]

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

 

[T. Valko]

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.

 

[MichaelR]

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]

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

 

[MichaelR]

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]

You're welcome. Thanks for the feedback!