mardi 27 novembre 2018

How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel Part 3

This is the third and final part of a series of questions on how to count entries in a matrix based on multiple conditions, only some of which needed to be true to qualify (Part 1), and when dealing with non-sequential columns (Part 2).

There have been some creative solutions that you can apply beyond the current context, and some fantastic camaraderie along the process.

The final step is this. Take the following matrix:

A B C D E F G H
4 2 2 2 1 4 2 4
  5 2 1 3 4   1
3 2 1     1 1 3
1 2 3 5 3 2 2 2
3 3 1 2 2 2 2 2

The goal is to count the number of rows that meet the following criteria:

  1. They contain entries for columns A:C and F:H (so row 2 should be ignored. Note row 3 has missing entries in columns D & E but this is irrelevant).
  2. They contain entries in columns A:C that meet at least one category-specific criteria (e.g., either column A = >2, column B criteria = >2, or column C = <2), AND
  3. They contain entries in columns F:H that do not meet any of the following criteria: column F criteria = >2, column G = >2, and column H = <2. NB. While the criterion values are the same between conditions 2 and 3, they could differ, so one's code should be flexible enough to cope with this).

The answer for the current matrix is 2 because row 3 and row 5 both have full data for the relevant columns (A:C and F:G), and they meet at least one of the criteria for columns A:C (e.g., A3 (3) and C3 (1) in row 3, and A5 (3), B5 (3), and C5 (1) in row 5) and do not meet any criteria for columns F:G.

I strongly recommend checking out XOR LX's creative approach to the question (only one set of criteria and sequential columns), as well as Tom Sharpe's excellent progression (for non-sequential columns but still one set of criteria), not least because you are guaranteed to learn something from their ingenuity.

I will update the question title names to reflect the best solution in each case.

Many thanks and let the games begin!

Aucun commentaire:

Enregistrer un commentaire