How to work with complex COUNTIFS
Requirement:
In the attached sheet the user has shown 4 examples and desired answers. Basically the user has 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, the user has worked through with text narration, why the "1" or "0" resulted?
Solution:
=--(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))))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to work with COUNTIF with multiple criteria |
| How to use Index/Countif based on multiple factors including range |
| How to COUNTIF using input cell as range depth |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
