I use Excel all the time -- how did I never know about array formulas, a.k.a. CSE formulas or "super formulas"???
I need to check that both data in Columns B and C match the criteria of specific Code and Score, and for those that match both criteria, I need to SUM the lenths in Column A.To do this you need to use an Array Formula, also known as a CSE Formula or even a Super Formula. This is a formula that requires you to press Ctrl+Shift+Enter after typing it, instead of just Enter.
Example
If you wanted to sum together all Lengths where the Code is 4a and the Score is 2, here is the formula you can to use:=SUM((B2:B11="4a")*(C2:C11="2")*A2:A11)
This will find that rows 4 and 11 contain both criteria at the same time, and will thus return the sum of lengths in those columns, i.e. A4 and A11, which is 141.09m + 731.38m = 872.47m.The formula looks a little strange and probably not what you'd expect; however, it works very well, as long as you remember to use Ctrl+Shift+Enter instead of Enter when inputting the formula.
Awe. Some.
