Conditional occurrences count

Would you manually add the results you expect please. It's always easier to answer if we know the final destination.

This proposal adds a helper column (K) to the table.
The formula for the helper column is:

Formula:

Please Login or Register  to view this content.

The formula for the Test column is:

Formula:

Please Login or Register  to view this content.

Note that the beginning date is placed in cell O1 instead of being hard coded into the formula so that it can be easily changed.
Let us know if you have any questions.

The following formula counts the invoices once and the credits once while still yielding an answer of 15:

Formula:

Please Login or Register  to view this content.

Note that you may need to use semicolons instead of commas based on whether one and one-tenth is written 1,1 instead of 1.1
Let us know if you have any questions.

Hello again,

it this way the formula is cleaner for me. Thank you.

Anyway it does not work in the correct way for all the cases that I have. On the second sheet, I put another example for another product.

The final result should be 1 and not 0.

The last two rows are excluded from the count due to the dates < "19/12/2021".

The fifht and sixth rows is the same invoice (10650), the part number is present twice, so the helper column do the right thing, I think, the total sold quantity is 2.
In the fourt row, we have a credit for one and the same in the second row. These 2 cancels the 2 of the invoice 10650.
So we have only one real sale of 2 quantities with the invoice 10728.

Any ideas how to change the formula?

I have also another question : if I would like to exclude from the count a company or more companies (A,B,C), what should I do in the count formula?

As to ideas to change the formula in the Test2 column, try:

Formula:

Please Login or Register  to view this content.

Note that the date Dec 19, 2021 is placed in cell O1 as my system reads 19/12/2021 as being the 12th day of the 19th month of 2021 which doesn't exist.
As to excluding companies, please provide a sample that illustrates the request. Remember to tell us the expected result.
Let us know if you have any questions.

The provided formula is not ok. On the first sheet, it should count 15 as well as the column "TEST" in order to be ok. In the column "Test2" it shows 85.
I am not looking for a formula that it would be ok for one case as it happens in the second sheet. The general idea is to count for any sold article within a period, how many times it has been sold. You can have an invoice for one piece or one for 10 pieces. It has to be counted once. From the count they are excluded the credit notes, because it is a return of a sale. So if I sold 10 with an invoice, you have to have a credit note for 10, to cancel it from the count. Of course the customer has to be the same. If happened like in the second sheet in which we have one invoice (10650) with two rows because the quantity sold is 2*-1, and afterwards we have two separate credit notes (10726 and 10820) with quantity +1, the final results is the same : 2*-1 -2*+1 = 0. It could happens also that a customer buys two pieces and return back only one, so you could have one invoice with -2 pieces and one credit note with +1 piece. So it should be counted as a complete sale (a +1 increment to the occurrences).

Regarding the excluding companies : to the existing formula we have to add an additional rule. Following what we already have on the first sheet as data, we have 15 occurrences. If I want to exclude the company "C", the occurrences will become 13. If I want to exclude "C" & "E", the occurrences will become 9. If I want to exclude only "B" (the customer with the credit note), the total occurrences will become again 15.

I hope that now it is more clear.

As to the excluded companies on the Лист1 sheet try:

Formula:

Please Login or Register  to view this content.

Note that P2:P4 may be used to name companies to be excluded from calculation.
I am confused as to why the Test2 column on Sheet1 should show 1 given the explanation in post #10.
I have put my understanding of how each row affects the count in column M and the running count in column N. Perhaps you could tell us where my understanding in incorrect so that we can attempt to write a formula that will work on both sheets.
Let us know if you have any questions.

Hello,

Probably I wasn't enough clear. I will try to explain in another way. Anyway, thank you for the exclusion company formula. It is clear.
I will start on the second sheet. We have invoice 10650 invoiced to company A, two rows with 1 sold piece. It is correctly counted once. But on this invoice we sold
-1-1=-2 pieces of 0634.349.853:000 to company A. Afterwards we have two credits 10726 & 10820 for +1 piece of 0634.349.853:000 to company A. The total amount of pieces that return back on stock are 1+1=2 that cancel the previous -2 sold pieces. So in this case one invoice is cancelled by two credit notes. We have to take in consideration also the sold quantities and the returned ones, not only the count of the invoices or the credit notes. With invoice 10728 we sold -2 pieces to company B, so this make the sales count +1 with a total amount of sold pieces -2.

Just to be sure that you will understand me. If on invoice 10650 the total sold quantity to company A was -3, afterwards with the two credit notes which the total returned quantity is +2, -1 quantity will remain sold to company A, so that means a +1 sales count that summed with the other one of company B, it will become +2. The total sold pieces will be -3 (-2 to company B and -1 to company A).

I have reached out to the other contributors to see if one of them may be able to help.

"19/12/2022" is a text string and all of the values in the document_date column are numbers (days since 1/1/1900).
DATEVALUE converts the text string into a number so that it can be compared to the values in the document_date column.
You could also use the DATE function and manually fill in the year, month and day values.
I hope that makes sense.