Multi level sort

Hi, welcome to the forum

Looks like your attachment did not come through, and many members are unable to see pics, so can you try the upload again please?

The data:

HTML Code: 

Does this make more sense?

Aims of the sort:

Stage one: “C” should equal the number of unique values of code “B” from linked to location value of “A”, both ar text strings but must return a number as the total unique values present.
ie: if three values of “B” are are associated with a value of AA01 in “A” this would = 3

Stage two: “E” is the summed score of SR2016 values and should equal all values of “D” that are associated with a unique location value in “A”
ie: if three "B" codes are present in location “A3” this would = 3

Mr ferret ~

If I understand correctly you want to count the number of unique codes in column B that are associated with each location in column A. For that part of the solution I used two helper columns (which could be hidden for aesthetic purposes) and the formula:

Formula:

=COUNTIFS(A$2:A$10,A2,G$2:G$10,1)

I came up with a different value for location AA04 as there are two unique codes associated.
For column E try:

Formula:

=SUMIFS(D$2:D$10,A$2:A$10,A2)

This does give some different values from those listed in post #3, however the values yielded by the formula seem to be correct, again assuming that my understanding of the OP is correct.
Let me know if you have any questions.

You're Welcome, thank you for the feedback and marking the thread 'Solved'. I hope that you have a blessed day.

OK not quite working yet i have realized...
as the scores of duplicate combos are still being counted!
Adding a row:[11]

Formula:

A=AA02 B=NBN002 D=8 (ie the same as Row3) G=2 

E should = 20 (as it does currently ie the sum of unique values in D3+D8+D9)
...but infact E=28 (counting the duplicate of D11)
Hope that makes sense
Mr Ferret

This is a response to post #7, not sure that I understand post #8. Perhaps if you could fill in the expected values for column C it will be clear what you want.
Modify the formula for G2 and down to:

Formula:

=COUNTIFS(F$2:F2,F2)

Modify the formula for E2 and down to:

Formula:

=SUMIFS(D$2:D$100,A$2:A$100,A2,G$2:G$100,1)

Let me know if you have any questions.

After further consideration it seems that the following formula in C2 and copied down will give you the values your want:

Formula:

=COUNTIFS(A$2:A$100,A2)-COUNTIFS(A$2:A$100,A2,G$2:G$100,2)

Let me know if you have any questions.

Im not quite sure what this change does im afraid...
and since the last post have corrected (abet in a little long winded fashion) to make Col C show the correct number using:

Formula:

=COUNTIFS(A:A,A3,K:K,1)

this works by adding three new cols:
the first col filters for values other than 1 using:

Formula:

=IF(COUNTIF(G:G,G3)>1,IF(COUNTIF(G$3:G3,G3)=1,"1",""),"")

the second filters the duplicate sets and labels the first duplicate in each set as 1:

Formula:

=IF(H3>=2, "", "1")

and the third combines these into a list of all unique rows to count the values in C:

Formula:

=I3&J3

what i have not managed yet is to make Col E be filtered by this new list of unique values: See below.

PS i have adder more duplicates so its clearer what the issue is

Try pasting the following formula in E3 and copy down:

Formula:

=SUMIFS(D:D,A:A,A3)-SUMIFS(D:D,A:A,A3,I:I,1)

Let me know if you have any questions.

That works a treat, thankyou kindly!
Well thanks to you I now have a working solution, I wonder if can streamline all these "working" columns (G-K) into fewer with a single formula...

Originally Posted by Mr Ferret

I now have a working solution, I wonder if can streamline all these "working" columns (G-K) into fewer with a single formula...

My personal opinion is that it isn't worth the effort. Excel provides 16,384 columns so using 5 for helpers isn't a big deal. Secondly, what you have now is kind of like a car with a carburetor, easier to fix if it starts acting up/needs an adjustment. Go to a complicated single formula solution and its like the new cars with the solid state circuitry/computer chips, you'll probably need some help tuning it if *anything* happens. I hope that you have a blessed day.