Countifs a certain value is present in a certain range

John_356

New Member
Joined
Jan 17, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    39.5 KB · Views: 4

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

John_356

New Member
Joined
Jan 17, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,088
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top