If cell has numbers and specific text, sum numbers with multiple criteria

To try to answer the question I think you are asking: in your version of Excel, an array formula will not be recognised automatically, so you have to tell Excel how to process it by entering it with CTRL+SHIFT+ENTER. If you don't only the top row of each array will be processed, not the whole array, and this will lead either to error messages or the wrong result (or, just occasionally, the right result by chance).

In newer versions you don't have to prompt Excel: it makes a decent job of working out what needs to be treated as an array formula or not.

To see the difference, enter an array formula WITHOUT CTRL+SHIFT+ENTER and then run it through Evaluate Formula (limit the size of the arrays in the test data to see what's happening more easily). Now run the same formula through having entered it correctly. You should see and hopefully understand a significant difference in behaviour.

@AliGW

Okay thanks, this makes more sense. I didn't even know the evaluate function existed so I'm learning!
I must of just got lucky with some of my other formulas with arrays that worked

So essentially anytime I'm entering a formula with an array (i.e not just the 1 row), because of my early version of excel, I need to use CTRL+SHIFT+ENTER for it to analyse that array correctly?