I've got the following formula.

=SUM(COUNTIFS(AllData!E:E,{"*HomePatient*","*Home Patient*"},AllData!J:J,{"*Consumable*","*Machine*"},AllData!$C:$C,">="&AG5,AllData!$C:$C,"<="&AH5   0.999988,AllData!$I:$I,"N"))

and basically what I want it to count is Count if:

1. In column E it can find the text "HomePatient" or "Home Patient"
2. In column J it finds the text "Consumable" or "Machine"
3. it's between two dates I've given it.
4. In column I there is the letter "N" in the cell.

My problem is that I can see that the formula works if I have only condition 1 or 2 on it, but if I have both conditions in the formula at the same time then the result is not correct.

What am I doing wrong?

CodePudding user response：

Transpose one of your criteria arrays such that it is orthogonal to the other.

So, assuming the row separator for your version of Excel is the semicolon:

Either:

Amend {"*HomePatient*","*Home Patient*"} to {"*HomePatient*";"*Home Patient*"}

Or:

Amend {"*Consumable*","*Machine*"} to {"*Consumable*";"*Machine*"}

By doing so, you ensure that all four possible combinations resulting from the two arrays are considered. If instead the two criteria arrays are of the same orthogonality, i.e. both vertical arrays or both horizontal arrays, then corresponding elements only within each array are considered, so when you employ

{"*HomePatient*","*Home Patient*"} and {"*Consumable*","*Machine*"}

each element from the first array is 'paired' with the corresponding element from the second. As a result, the above would lead to a computation of only

"*HomePatient*" with "*Consumable*"

and

"*Home Patient*" and "*Machine*"

effectively ignoring the other two possible combinations.