• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.

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

Thank you ever so much guys.
 
Last edited by a moderator:

Verno2021

New Member
By the way guys. The faces must be a bug as I have already tried editing my comment. It is post to say P4 colon P 37. ?!
 

vletm

Excel Ninja
Verno2021
If You use tags with Your formulas then those would be more readable.
You could find instructions for those eg from:
Hint: With a sample Excel-file others could test Your ... formulas much better.
 

KenU

Active Member
If I have understood correctly, the following should work. It is very difficult to say without the sample workbook for testing.

For cancel after one month:
=SUMPRODUCT((L4:L37<>"")*(M4:M37>0)*SIGN((N4:N37=0)+(N4:N37=""))*SIGN((O4:O37=0)+(O4:O37=""))*SIGN((P4:P37=0)+(P4:P37=""))*SIGN((Q4:Q37=0)+(Q4:Q37=""))*SIGN((R4:R37=0)+(R4:R37=""))*SIGN((S4:S37=0)+(S4:S37=""))*SIGN((T4:T37=0)+(T4:T37=""))*SIGN((U4:U37=0)+(U4:U37=""))*SIGN((V4:V37=0)+(V4:V37=""))*SIGN((W4:W37=0)+(W4:W37=""))*SIGN((X4:X37=0)+(X4:X37="")))

For cancel after two months:
=SUMPRODUCT((L4:L37<>"")*(M4:M37>0)*(N4:N37>0)*SIGN((O4:O37=0)+(O4:O37=""))*SIGN((P4:P37=0)+(P4:P37=""))*SIGN((Q4:Q37=0)+(Q4:Q37=""))*SIGN((R4:R37=0)+(R4:R37=""))*SIGN((S4:S37=0)+(S4:S37=""))*SIGN((T4:T37=0)+(T4:T37=""))*SIGN((U4:U37=0)+(U4:U37=""))*SIGN((V4:V37=0)+(V4:V37=""))*SIGN((W4:W37=0)+(W4:W37=""))*SIGN((X4:X37=0)+(X4:X37="")))

I hope that helps!

Regards,
Ken
 

Verno2021

New Member
If I have understood correctly, the following should work. It is very difficult to say without the sample workbook for testing.

For cancel after one month:
=SUMPRODUCT((L4:L37<>"")*(M4:M37>0)*SIGN((N4:N37=0)+(N4:N37=""))*SIGN((O4:O37=0)+(O4:O37=""))*SIGN((P4:P37=0)+(P4:P37=""))*SIGN((Q4:Q37=0)+(Q4:Q37=""))*SIGN((R4:R37=0)+(R4:R37=""))*SIGN((S4:S37=0)+(S4:S37=""))*SIGN((T4:T37=0)+(T4:T37=""))*SIGN((U4:U37=0)+(U4:U37=""))*SIGN((V4:V37=0)+(V4:V37=""))*SIGN((W4:W37=0)+(W4:W37=""))*SIGN((X4:X37=0)+(X4:X37="")))

For cancel after two months:
=SUMPRODUCT((L4:L37<>"")*(M4:M37>0)*(N4:N37>0)*SIGN((O4:O37=0)+(O4:O37=""))*SIGN((P4:P37=0)+(P4:P37=""))*SIGN((Q4:Q37=0)+(Q4:Q37=""))*SIGN((R4:R37=0)+(R4:R37=""))*SIGN((S4:S37=0)+(S4:S37=""))*SIGN((T4:T37=0)+(T4:T37=""))*SIGN((U4:U37=0)+(U4:U37=""))*SIGN((V4:V37=0)+(V4:V37=""))*SIGN((W4:W37=0)+(W4:W37=""))*SIGN((X4:X37=0)+(X4:X37="")))

I hope that helps!

Regards,
Ken
Hi Ken,

Thank you so much for your help. This formula works brilliantly on the June and July Tab but does nothing on the Aug tab. I have stopped proceeding entering this formula because I am not sure if it is working. However, not at all knocking your skills. It is difficult if I have not shared the Excel sheet with you.

I have now shared it with you. I hope you don't mind. If you are able to tell me what is stopping this formula from working or what formula I should write instead, that would be brilliant. I am just starting out in this field but I can tell from your comment that you must be experienced at what you do.

Again, thank you ever so much. This sheet has been playing on my mind for a while now.

Regards,

Craig
 

Attachments

Verno2021

New Member
KenU, I am very sorry. My mistake. Your formula is perfect. Nothing needs fixing. Thank you ever so much, you have no idea how helpful this has been.
 
Top