How to More Elegantly Sum the Values in a Column Based on the Values in Another Column

[DavidWF]

I have a worksheet where row 1 is a header row and data start in row 2.

Col M contains numeric values; some cells may be blank

Col T contains code letters; if a row has no value displayed in col M then the corresponding cell in col T will also be blank.

I want to find the sum of all the values in col M between cell M2 and the last non-blank cell where the value in col T is not "X".

I'm currently achieving it by selecting a row that's way beyond where I'm ever likely to get to and summing everything in col M, then deducting from that the sum of the values in col M where the corresponding cell in col T = "X":
=SUM(M2:M10000)-SUMIF(T2:T10000,"X",M2:M10000)

It works but I'm sure there's a more elegant way to achieve what I'm after but it's beyond my knowledge; guidance on replacements for T10000 and M10000, or on an overall better method, will be much appreciated.

 

[arthurbr]

Perhaps =sumproduct(--(T2:T1000="x")*M2:M1000)

 

[jasonb75]

Perhaps

=SUMIF(T:T,"<>X",M:M)

Unless you're using 1000's of formulas, full columns are not generally an issue with SUMIF or similar functions. Anything like SUMPRODUCT that uses an array will be noticeably slower.

 

[DavidWF]

Thanks arthurbr, but it returned #VALUE!

jasonb75 - that is much more elegant than my formula - and it works. Great! I've modified the worksheet and can now go to bed happy.

My thanks to both of you for the very prompt response.