0

Count and Filter Total Binaural Rate

Hi,

I was hoping someone could lend me hand.

I need to count all the sales invoices that have a posting group of X. There are some more filters all within the same table like date, vendor etc but that's not a problem. The problem I have is I need to only count invoices that have 2 line items equal to Posting group x. There may be 8 items on an invoice which is fine as long as two of those invoice lines contain item Z with posting group X. Each of the two invoice lines should only have a quantity of 1 thus giving me the Bin-aural Rate for a specific customer (number of posted invoices that sold two of the same items)

Any ideas on how I could do this?

The formula below gets me the number of posted invoices that have item X with an item category code of "HA". This isn't accurate because an invoice may have 1 or may have 6 items X's with item category code "HA" in them and I only want to count invoices with 2 unique invoice lines with a quantity of 1 on each line that have item category code "HA".

=NL("count","sales invoice line","Document No.","Company=",$D14,"Quantity","1","Posting Date",AL$8,"Clinic Code",$E14,"Type","Item","Item Category Code","HA")

Sorry if this was repetitive I didn't think I was explaining well.

Thanks everyone,

1 comment

Please sign in to leave a comment.