# COUNTIFS with multiple OR conditions

#### Verno2021

##### New Member
Hi there,

I hope you can help me as I have been struggling with this issue for weeks.

I am handling data for an insurance provider package supplier company. One of our company's providers in particular is complicated as there are several possible outcomes of a customers' journey. I have been brought on by the company to organise, and see what is happening our customers and their payments.

The sheet is virtually finished but I am struggling with a couple of columns. One column needs to tell us how many customers have cancelled their policy after one payment, and another column needs to tell us how many customers have cancelled their policy after second payment. This does need to be automated as there more data entries to make by the month, so the idea is that when data is entered in the first table, we get a tally in a second table.

The first column needs recognise when there is an entry in the Cancellation Confirmation Date column, when there is a value greater than 0 in the first month, and then checks if there is a 0 or no entry in the next months' columns then get counted in the tally.

The second column I am having problems with (tallying up how many customers cancel their policy after two payments) is to be formulated almost the same. Recognise when their is an entry in the Cancellation Confirmation Date column, there is a value greater than 0 in the first month column and the second month column, and that there is a 0 or no entry in the next months' columns.

The 0 condition is in case a 0 is entered in the next upcoming months. As £0 is £0 obviously we need to be aware of it.

Up to now, I have tried

=SUM(COUNTIFS(L4:L37,"<>",M4:M37,">0",N4:N37,{"",0},O4:O37,{"";0},P4:P37,{"";0},Q4:Q37,{"";0},R4:R37,{"";0},S4:S37,{"";0},T4:T37,{"";0},U4:U37,{"";0},V4:V37,{"";0},W4:W37,{"";0},X4:X37,{"";0}))

for cancellation after one payment

=SUM(COUNTIFS(L4:L37,"<>",M4:M37,">0",N4:N37,">0",O4:O37,{"";0},P4:P37,{"";0},Q4:Q37,{"";0},R4:R37,{"";0},S4:S37,{"";0},T4:T37,{"";0},U4:U37,{"";0},V4:V37,{"";0},W4:W37,{"";0},X4:X37,{"";0}))

for cancellation after second payment.

I then learnt that OR conditions can only handle two separate OR conditions. So I have tried this

=SUMPRODUCT(ISNUMBER(MATCH(N4:N37,{"";0},0))* ISNUMBER(MATCH(O4:O37,{"";0},0))* ISNUMBER(MATCH(P4:P37,{"";0},0))* ISNUMBER(MATCH(Q4:Q37,{"";0},0))* ISNUMBER(MATCH(R4:R37,{"";0},0))* ISNUMBER(MATCH(S4:S37,{"";0},0))* ISNUMBER(MATCH(T4:T37,{"";0},0)) * ISNUMBER(MATCH(U4:U37,{"";0},0))* ISNUMBER(MATCH(V4:V37,{"";0},0))* ISNUMBER(MATCH(W4:W37,{"";0},0))* ISNUMBER(MATCH(X4:X37,{"";0},0)))

but COUNTIFS doesn't seem to be combined with this formula, yet I need the COUNTIFS really because the first two/three conditions to be met are not OR condition related.

Thank you ever so much guys.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
i understand that the data you are working with might be sensitive, can you share an altered example of the data you are working with?
might be easier to come up with a solution.

i understand that the data you are working with might be sensitive, can you share an altered example of the data you are working with?
might be easier to come up with a solution.
Hi ExcelLoki,

I have removed sensitive data. Thank you so much for wanting to take a look at it. This is really appreciated. Really, you only need to be concerned with the second and third image as this holds the columns mentioned in my question.

Again, thank you so much.

Last edited by a moderator:
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: COUNTIFS with multiple OR conditions
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

if you're able to add a column here's a simple solution
-------------------
 month 1 month 2 month 3 month 4 month 5 month 6 Total months worst case scenario (limbo phase plus cancellations) customers continuing with policy (after still to be paid, unpaid, bad debt) customers cancelling policy (after still to be paid, unpaid, bad debt) customers cancelled after one payment customers canceled after two payments cancellation confirmation date paid oct paid nov paid dec paid jan paid feb paid march months paid 3 1 0 10/1/2021 16.31 1 12/1/2021 32.63 32.63 2 1/1/2022 36.53 36.53 36.53 3 15.7 1 10/1/2021 31.42 1 32.63 1 31.41 1 10/1/2021 32.63 1 0

----------
total months
=COUNTIF(M4:R4,">0")

customers cancelled after one payment
=COUNTIFS(L4:L13,"<>",S4:S13,"1")

customers canceled after two payments
=COUNTIFS(L4:L13,"<>",S4:S13,"2")

Replies
1
Views
219
Replies
4
Views
257
Replies
3
Views
224
Replies
7
Views
339
Replies
0
Views
268

1,203,488
Messages
6,055,716
Members
444,811
Latest member
NotJack

### 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.

### Which adblocker are you using?

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

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