Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

OK, in that case we just need a simple tweak...

=SUMPRODUCT(SIGN((Inventory!J3:J10000>625)+ISNUMBER(SEARCH("CA",Inventory!M3:M10000))+ISNUMBER(SEARCH("CA",Inventory!O3:O10000))))

I'm assuming that you want a row by row count and not a cell by cell count.

For example, if this is your data on row 5:

J5 = 1000
M5 = XX
O5 = CA here

That gets counted as 1 even though 2 cells meet the conditions.