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

Cell I3 in the lookup sheet contains text AFTER the number as well. Will the number always be 3 digits?

I think you are making this far too complicated. If it were me, I would not have both text and numbers in the same cell.

No. And that's why I'm stepping aside on this one - sorry. Hopefully someone else will be able to help you.

EDIT: Just seen your latest post - it might help.

No - IP3 shows just SP 137 now!

This is as close as I can get you I'm afraid:

=SUMPRODUCT((INDIRECT($E$5&"[Style]")=G6)*(INDIRECT($E$5&"[Attack/Defense]")="Attack")*((LEFT(INDIRECT($E$5&"[[1]:[13]]"),2)="SP")),IFERROR(VALUE(MID(INDIRECT($E$5&"[[1]:[13]]"),4,3)),0))

That still returns #value error.

No, it doesn't! See attached.

in theory it should spit out the answer but it doesnt?

In practice, the null values ("") in the arrays cause #VALUE! errors.

I'm done with this one - sorry.

I've put out a call for help to the community. Good luck with this.

thankyou for trying!

Sorry Im a bit confused, This formula you added to the workbook doesnt work if you click on the formula to look at it, it removes the brackets from the beginning and end and goes back to #value error?

**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).

Try this ARRAY formula in L6, copy down

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

Okay, so I still don't fully understand how the array formula is different to the standard formula in my particular case, but if I put the original formula that I wrote into an array formula it works . Will this impact/give me issues if I run with the original formula from the first worksheet I uploaded? I know its overly complicated, but if it doesn't impact it I think that the first formula might give me a bit more variation to create more criteria, i.e another column that calculates the hits with opponent shield (OSH) rather than calculating the specials.

Which of us are you addressing?

You need to test this on other versions of your workbook as you are the one who has the data. That's not our job, so try it, test it and come back to us with any issues that arise.

**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).[/QUOTE]

*Edit: I just read your last post, so that array formula is the same as my original and just adds the "if theres an error, = 0 " is that correct?

Edit Again: Apologies I didn't realise it was a different person.

Originally Posted by kvsrinivasamurthy

Try this ARRAY formula in L6, copy down
.

Thankyou!!! Ive tested this and it looks like it works perfectly, Im also able to change some of the critieria to fit different questions.

Just so I can try and understand it a bit better what is the reason for wrapping it in the ifferror?

So I am assuming you think there is some sort of issue with the formula, otherwise you would have just said use this, hence the question.

No, it simply means what I said: it's the closest I can get you to a solution. I have my limits! I am no Excel expert (I'm a retired languages teacher): I offer my expertise here where I can and am able, for the most part, to help those I engage with to a conclusion. On this occasion I'm not able to get any further. This does not mean I think there is something WRONG with the formula, simply that it might not do EVERYTHING you want it to do. This is why I put out a call for help to the wider community.

Again - you need to stress test these things and then come back to us with issues, if they arise.

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.

Asking questions and getting help are not bad . They definitely improve the knowledge.
Thanks for feedback and rep.

@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?

Yes, that's correct. If you enter a formula that does NOT need to be entered as an array as one, it won't hurt, so if in doubt, array enter. Of course you need to array enter and THEN drag copy the formula across or down.

Basic SUMIF(S), COUNTIF(S), etc. do not need array entering.

The evaluate formula function is really helpful as long as you limit your test arrays - because you can't enlarge the dialog box, it's better this way.

I'm glad you've learnt something: the teacher in me wants that to happen!