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

[Alessandro05]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 923, align: left"]
<tbody>[TR]
[TD]Contract #[/TD]
[TD]Unique Id[/TD]
[TD]Vendor Name[/TD]
[TD]Business Unit[/TD]
[TD]Real/Assigned CW ID[/TD]
[TD]Custom Unique ID[/TD]
[TD]Sent To Legal[/TD]
[/TR]
[TR]
[TD]CW1[/TD]
[TD="align: right"]268[/TD]
[TD]Vendor 1[/TD]
[TD]Sales[/TD]
[TD]Real[/TD]
[TD]Vendor 1 CW1 268[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CW2[/TD]
[TD="align: right"]5453[/TD]
[TD]Vendor 2[/TD]
[TD]Ops[/TD]
[TD]Not Available[/TD]
[TD]Vendor 2 CW 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]CW1[/TD]
[TD] 89374[/TD]
[TD]Vendor 1[/TD]
[TD]Ops[/TD]
[TD]Not Available[/TD]
[TD]Vendor 1 CW 1 89374[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CW4[/TD]
[TD="align: right"]5675[/TD]
[TD]Vendor 4[/TD]
[TD]Ops[/TD]
[TD]Not Available[/TD]
[TD]Vendor 4 CW 4[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CW5[/TD]
[TD="align: right"]521[/TD]
[TD]Supplier 1[/TD]
[TD]Ops[/TD]
[TD]Real[/TD]
[TD]Supplier 1 CW 5[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CW6[/TD]
[TD="align: right"]595[/TD]
[TD]Supplier 2[/TD]
[TD]Sales[/TD]
[TD]Real[/TD]
[TD]Supplier 2 CW 6[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hello, Need help for work... been trying for two days.....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-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)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]So columns a and G are the areas of focus. Can anyone help?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This must be a formula as the line items will continuously grow

Thanks so much for helping me[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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...