Countifs a certain value is present in a certain range

[John_356]

Good day,

Here's a little background just to make things slightly more engaging and easier to contextualize:

I'm managing from a distance a small company based in the Middle East and because of the ways they do business over there, payments are made without mentioning the invoice number or any payment reference. Fortunately we do have an accounting software from which I can make CSV extractions of customer payments, customer account balances and invoices. The hardest part is done, which is to figure out a way to implement a FIFO logic with the numbers and automate it.

As you can see from the screenshot below, the macro gathers every payment (P1, P2...) for each customer ID and calculates what is still owed etc, not really rocket science

I'm currently struggling with crazy nested IF functions so if I could use a COUNTIFS function to check whether a payment (P1, P2 etc...) has already been accounted for that would make my life so much easier.

FYI P1, P2, P.. columns should only have one value per ID so I would like to use a COUNTIFS formula to check if the payment has already been accounted for. I've changed the font to bold red of some P3 payments which have been accounted for many times in this example's sake.

At your disposal should you need more info from me.

Cheers

 

[John_356]

It goes without saying all my attempts to use a COUNTIFS formula as described above have failed, would be very grateful if someone can point me in the right direction