How to manually set order in excel?-CodePudding

I am trying to count SUM of numbers highlighted in blue if the month is bigger or equal to M1 and smaller or equal to M2. Unfortunately excel thinks, that M10-M13 is in between M1 and M2. Any tips how can I manage to write my formula or somewhere change the default order in excel to do this properly?

CodePudding user response：

If you have control over the setup, prefix the month number with a zero for January to September.

2021 M1 would become 2021 M01

Excel will treat the data as ordered from 2021 M01 to 2021 M12.

If you don't have control over the setup, you can reformat the headings via a formula to insert a zero before the month number.

=IF(LEN(F4)=7,CONCAT(LEFT(F4,6),0,RIGHT(F4,1)),F4)

Then use the reformatted headings in your formula.

CodePudding user response：

You could use:

Formula in B5:

=SUM(INDEX(5:5,MATCH(B2,4:4,0)):INDEX(5:5,MATCH(C2,4:4,0)))