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.
Very interesting question.
Array formula**:
=SUM(N(MMULT(IF(MMULT(N(A1:D4=""),TRANSPOSE(COLUMN(A1:D4)^0))=0,COUNTIF(OFFSET(A1,ROW(A1:D4)-MIN(ROW(A1:D4)),COLUMN(A1:D4)-MIN(COLUMN(A1:D4))),{">2",">2","<2",">4"}),0),TRANSPOSE(COLUMN(A1:D4)^0))>0))
Edit: Without the presence of blanks within the range, this could be simplified greatly to:
=ROWS(A1:D4)-COUNTIFS(A1:A4,"<=2",B1:B4,"<=2",C1:C4,">=2",D1:D4,"<=4")
since, logically, to obtain the number of rows for at which at least one condition is true, we can calculate the number of rows for which none of those conditions are true and subtract that value from the total number of rows.
In fact, it may well be possible to adapt this set-up to account for blanks as well. Will look into it.
Update: Indeed, I believe this will work:
=ROWS(A1:D4)-COUNTIFS(A1:A4,"<=2",B1:B4,"<=2",C1:C4,">=2",D1:D4,"<=4")-COUNT(1/N(MMULT(N(A1:D4=""),TRANSPOSE(COLUMN(A1:D4)^0))>0))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Can I offer as a kind of lemma to @XOR LX's brilliant answer that you might be able to do this:
=COUNTIFS(A1:A4,"<>",B1:B4,"<>",C1:C4,"<>",D1:D4,"<>")-COUNTIFS(A1:A4,"<=2",B1:B4,"<=2",C1:C4,">=2",D1:D4,"<=4")
i.e. The number of rows not containing a blank minus the number of these which don't fulfil any of the conditions.
