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.

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.

Last edited by a moderator: