dimanche 25 novembre 2018

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

I am trying to count the number of rows that: 1) have entries for all columns, and ii) have at least one value that meets a column-specific criterion.

Here's an example:

A B C D
4 4 3 5
2 2 2 2
3 1 2 5
1   3 2

  • Column A Threshold: >2
  • Column B Threshold: >2
  • Column C Threshold: <2
  • Column D Threshold: >4

The answer for this example is 2 because 2/4 rows include at least 1 value that meets a column threshold. Specifically, row 1 has 3 values that meet the column thresholds and Row 3 has 2 values. Row 2 has no values that meet the column-threshold, while row 4 should not be counted because it does not contain entries for all columns, e.g.:

A B C D
T T F T
F F F F
T F F T
F F F F

So I don't want to count how many times a specific column threshold has been met in each row, but whether at least 1 has been met in each row.

I have a hunch that SUMPRODUCT could be useful for this issue, but I do not know how to add an OR criterion for only some criteria (for instance, the example described here of using a "+" in SUMPRODUCTS as an OR function counts both the criteria).

Your thoughts would be most welcome.

Aucun commentaire:

Enregistrer un commentaire