2 step netting process - possible? (using SUMIF?)

[donyc]

Hi all, I am working on a project on identifying sales by location and need some help as I am stuck on a formula. Would very much appreciate any input/advice

Thanks so much

Issue: I have thousands rows with different cities and different endings in .X or .Y or .AB and so forth.
Example: NewYork.X (for sake of easy I will call "NewYork" - part 1 and ".X" - part 2)
Formula I have so far: =IF(SUMIF($B$2:$B$13,"*"&LEFT($B2,FIND(".",$B2,1)-1)&"*",$C$2:$C$13)=0,"NETS to zero","")

What I am looking to do: - I am also attaching a spreadsheet with all of the below, which might be easier to view.


1) Identify any city which has same PART1 and also PART2 that ends in .X and.Y and this combination nets to 0 so for example:

Row City Sales Output My comments
1 NewYork.X 100 NETS to zero OK - assigns correctly
2 NewYork.Y -100 NETS to zero OK - assigns correctly

2) Below example does not net down which is what I want. Good with this.
3 JerseyCity.X 50 OK leaves blank comments
4 JerseyCity.Y 20 OK leaves blank comments

3) Below example involves 3 rows with same idea.. Again this is fine

5 Trenton7.X -100 NETS to zero OK - assigns correctly
6 Trenton7.X 50 NETS to zero OK - assigns correctly
7 Trenton7.Y 50 NETS to zero OK - assigns correctly

4) Below 2 examples I have trouble with where the formula does not work
8 Rye.AB -20 NETS to zero should be blank as it does not end in .X or .Y
9 Rye.AB -80 NETS to zero should be blank as it does not end in .X or .Y
10 Rye.AB 100 NETS to zero should be blank as it does not end in .X or .Y
11 Liverpool1.X 500 NETS to zero while both are Liverpool1 and net to 0, I only need it know if its Liverpool1 and if it is ending with .X and .Y netting to 0
12 Liverpool1.X -500 NETS to zero while both are Liverpool1 and net to 0, I only need it know if its Liverpool1 and if it is ending with .X and .Y netting to 0

[Special-K]

None of your examples end in .PAID or .UNPAID

Question:
Your example data says

Trenton7.X
Liverpool1.X

Yet the results should be NETS to zero (Trenton) and blank (Liverpool)

Given that sparse information how can we tell the Liverpool result should be blank? It looks just like the Trenton data to me.

[donyc]

sorry.. i fixed part 4) it had incorrect comments.. I also reuploaded new spreadsheet with correct comments what I am trying to do

to answer your question

Trenton7.X has another combination Trenton7.X(2rows) AND Trenton7.Y, so any part that has Trenton7 AND has ending in .X and .Y
- I only need data to be computed on such combination

Liverpool1.X you will see has just 2 rows BOTH ending in .X so it does not have .Y in the end.

[donyc]

maybe there is a way to use pivot tables? but I would like to avoid it if possible.. any thoughts?