Complex COUNTIFS?

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I've tried COUNTIFS to solve my issue to no avail. In the attached sheet I've shown 4 examples and desired answers. Basically I have 3 values that reside at the top of a table, with data beneath. A formula in G1 (example 1) should provide a unique number 0 or 1. In the "Blue" quotation box, I've listed the formula parameters. For each desired answer, I've worked through with text narration, why the "1" or "0" resulted? Hope you can follow this?
    Cheers [ATTACH]n1198832[/ATTACH]

  • Does this work?


    =--(OR(C1<>C3,AND(COUNTIFS(C3:C8,C1,D3:D8,"<>"&D1,E3:E8,"<"&E1)=COUNTIF(C3:C8,C1),COUNTIFS(C3:C8,C1,E3:E8,"<"&E1)=COUNTIF(C3:C8,C1))))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!