Count Distinct Values after Filter

[rrlee123]

Hello, I can usually find what I need to write complex formulas, usually on this forum, but this one has had me stumped for quite some time.

I have data contained in a Table (Table1) with named ranges and need to count distinct document numbers where it does or doesn't meet certain criteria. I've been able to construct this formula to count the document numbers where it meets 4 different criteria COUNTA(FILTER(T1_RefDoc_Num,(T1_Ref_Doc_Type="CC")+(T1_Ref_Doc_Type="CL")+(T1_Ref_Doc_Type="CR")+(T1_Ref_Doc_Type="CP"))). This gets me a count, but I need to count the distinct document numbers that appear several times in the data as well as exclude GL data not equal to 131000.15.

Basically, I need to count all distinct document numbers where the document type (T1_Ref_Doc_Type) is either CC, CL, CR or CP but doesn't belong to General Ledger number 131000.15 (T1_GL) and then count the distinct documents that are returned. I don't see how to upload a file, but have paired down the data significantly, if someone would be willing to take a look.

Thank you,
Rusty

 

[Fluff]

Maybe

rows(unique(FILTER(T1_RefDoc_Num,((T1_Ref_Doc_Type="CC")+(T1_Ref_Doc_Type="CL")+(T1_Ref_Doc_Type="CR")+(T1_Ref_Doc_Type="CP"))*(T1_GL<>131000.15))))

The 31000.15 may need to go in quotes.

 

[rrlee123]

rows(unique(FILTER(T1_RefDoc_Num,((T1_Ref_Doc_Type="CC")+(T1_Ref_Doc_Type="CL")+(T1_Ref_Doc_Type="CR")+(T1_Ref_Doc_Type="CP"))*(T1_GL<>131000.15))))

Thank you so much this worked!! I didn't think to even use ROWS...but I did get the 131000.15 using the * to work. I can't thank you enough!!!

 

[Fluff]

You're welcome & thanks for the feedback.