Counting Unique text values based on 1 and/or 2 criteria - other options didnt work.

[Alessandro05]

Contract # Unique Id Vendor Name Business Unit Real/Assigned CW ID Custom Unique ID Sent To Legal CW1 268 Vendor 1 Sales Real Vendor 1 CW1 268 Yes CW2 5453 Vendor 2 Ops Not Available Vendor 2 CW 2 No CW1 89374 Vendor 1 Ops Not Available Vendor 1 CW 1 89374 Yes CW4 5675 Vendor 4 Ops Not Available Vendor 4 CW 4 Yes CW5 521 Supplier 1 Ops Real Supplier 1 CW 5 Yes CW6 595 Supplier 2 Sales Real Supplier 2 CW 6 Yes <tbody> </tbody>
Hello, Need help for work... been trying for two days.....
-I am looking to count the unique values in column 1 (Contract #) that have been sent to the Legal department. The correct answer is 4 (Lines 1, 4, 5, & 6)
See... each line is a different file but not a different contract per se. I want to count the number of contracts and no the number of lines so if a contract ID shows up in column A more than once... it should only be counted once.
So columns a and G are the areas of focus. Can anyone help?
This must be a formula as the line items will continuously grow Thanks so much for helping me

<tbody>
</tbody>

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(G2:G7="Yes",IF(LEN(A2:A7)>0,MATCH("~"&A2:A7,A2:A7&"",0))),ROW(A2:A7)-ROW(A2)+1)>0,1))

Also, if you convert your data into a Table (Ribbon > Insert > Table), the range will automatically be adjusted as data is added/removed.

Hope this helps!

 

[Alessandro05]

Thanks for the quick reply. I am getting the same error. I been researching the web and a few examples are similar to yours.

=SUM(IF(FREQUENCY(IF(M11:M50 ="Yes",IF(LEN(G11:G50)>0,MATCH("~"&G11:G50,G11:G50&"",0))),ROW(G11:G50)-ROW(G11)+1)>0,1))

I used the above where Column G houses the "contract numbers" and column M houses the "Sent To Legal" The list is currently sitting from rows 11 through 50.
I am not understanding the "-Row(G11)" as pect of the formula either. Thanks again Domenic as this is work related and really getting to me!

 

[Domenic]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER? If done correctly, Excel will automatically place curly braces {...} around the formula.

 

[Alessandro05]

Yes I did and there are the correct brackets

 

[Alessandro05]

IT NOW WORKS. THANKS SO MUCH. The above formula works perfectly. Just need to reference the correct G11 and not G10

 

[Domenic]

That's great, glad you've sorted it out...