Formula not counting correctly with 2 "OR" conditions

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?

>Solution :

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*"}