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