Forum Discussion
Formula not working - is it because we are using an older Excel version?
SergeiBaklan JanePG Sergei, it would appear your formula also doesn't work for pre-DA and that is your answer Jane that it appears you have dynamic arrays and your husband does not. I suspect if you type =FILTER you will see information about it while your husband's version would only show FILTERXML as a valid function.
They are still rolling DA out so it is still possible your husband may get it soon.
As for your formulas please note the your "LEFT(... , 1)" is purely summing the first character and if those values exceed 1 digit OR if the corresponding values in the table change (e.g. 2today has "5" in col B) that formula will not work.
In general
=SUMPRODUCT(--LEFT($D$9:$D$12))shall work.
As variant
=SUMPRODUCT(INDEX($B$9:$B$11,MATCH($D$9:$D$12,$A$9:$A$11,0)))but I'm not sure how it works on pre-DA Excel
SergeiBaklan JanePG Sergei, it would appear your formula also doesn't work for pre-DA and that is your answer Jane that it appears you have dynamic arrays and your husband does not. I suspect if you type =FILTER you will see information about it while your husband's version would only show FILTERXML as a valid function.
They are still rolling DA out so it is still possible your husband may get it soon.
As for your formulas please note the your "LEFT(... , 1)" is purely summing the first character and if those values exceed 1 digit OR if the corresponding values in the table change (e.g. 2today has "5" in col B) that formula will not work.
Many thanks for your response re DA (and the Excel file), I now know far more about them and yes, my husbands version doesn't have =filter. His company are on the semi-annual enterprise channel where as I work in a school and am on current channel (and now have just gone to 2007)
Thanks for the comments about the =left formula, he only wants it to sum the first digit and it will only ever be 1 digit
Many thanks once again.
First formula shall work on any Excel. It could be complicated to extract most left number, not only first character, but I'm not sure that's the case.
