COUNTIFS with multiple OR conditions

[Verno2021]

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.

There must be a clever way round this. Please help.

Thank you ever so much guys.

 

[ExceLoki]

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.

 

[Verno2021]

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.

I am sorry if this is not helpful. I have tried operating the "Upload Mini-sheet" but it does nothing when I try opening it after downloading.

Again, thank you so much.

 

[Fluff]

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.

Be sure to follow & read the link at the end of the rule too!

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.

 

[ExceLoki]

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")