COUNTIF Formula Help

handmadetsunami

Hi

I am trying to work out how to do a a COUNTIF formula but I can't get it to work.

I have a sheet which contains some payment details and have created an exceptions report that shows invoices that have been submitted but not yet approved which works fine but we want a widget to show a figure of all the exceptions in this category.

When an invoice is submitted, the C Invoice Received (formatted as a date) column has a date added to it, the C Invoice Ref has the invoice ref added to it and the C Invoice Added has the checkbox checked.

After an internal process of approvals, the C Invoice Paid (formatted as a date) box gets completed with a date.

I want to be able to count the number of invoices submitted (C Invoice Added) but that haven't been approved (C Invoice Paid) but I am struggling to get a formula to work.

Some rows do not have invoices submitted as they are paid in a different way so I am only interested in the ones that have had invoices added but not paid (a count of 2 for 0007 and 0008)

I've attached a screenshot to show what I mean and any help would be appreciated.

Andrée Starå

Hi,

Try something like this.

=COUNTIFS([C Invoice Added]:[C Invoice Added]; 1; [C Invoice Paid]:[C Invoice Paid]; ISBLANK(@cell))

The same version but with the below changes for your and others convenience.

=COUNTIFS([C Invoice Added]:[C Invoice Added], 1, [C Invoice Paid]:[C Invoice Paid], ISBLANK(@cell))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

handmadetsunami

Hi Andrée,

Thank you for your help on this formula too! Works perfectly and does what I need it to do!

Kind regards,

Andrée Starå

Great!

I'm always happy to help!

Best,

Andrée