1

I'm trying to track my recent 12 months spend on an ongoing basis. Each month a new column is added for the new month, but I want Column B to show the sum of the most recent 12 months.

I've set up a sheet that can be edited at https://docs.google.com/spreadsheets/d/1GNAyLuRHDXJYpCndVIIovIB4_Y-QXgH2nOMn1OSNTVc/edit?usp=sharing

The formula that I've tried in cell B2 is (not working):

=SUM(QUERY(SORT(C2:AZ2, Column(C2:AZ2)*ISNUMBER(C2:AZ2), 0), "select * limit 12"))
Improve this question
2

1 Answer 1

Reset to default
1

Keeping in mind that we can't see your data or layout, when I merely "eye" this, it seems you should be using TRANSPOSE on the elements of your sort. Try this:

=SUM(QUERY(SORT(TRANSPOSE(C2:AZ2), TRANSPOSE(Column(C2:AZ2)*ISNUMBER(C2:AZ2)), 0), "select * limit 12"))

If that does not work, please consider sharing a link to your sheet (or a copy of your sheet, or a spreadsheet containing sample data representative of your sheet), being sure to set the link's Share permission to "Anyone with the link > Editor."

Improve this answer
7
  • This works perfectly. I had cells with no information (instead of zeros) that was throwing it off. Once I put zeros in, all is well. Thank you Erik!
    – Joe Manausa
    Commented Jan 7, 2021 at 13:27
  • 1
    Good to hear. Thank you for reporting back and for indicating to the team of volunteer contributors here that the issue has been satisfactorily addressed by marking a "Best Answer."
    – Erik Tyler
    Commented Jan 8, 2021 at 0:15
  • Erik, how would I adjust the formula to (instead of the last twelve), ignore the last cell and get the 12 before it? I using this for ROI calculations, and my last cell is a "work in progress" during the month, so I want my ROI calculation to always be through the previous month.
    – Joe Manausa
    Commented Jan 8, 2021 at 21:26
  • 1
    Joe, your file is in your Trash. You'll need to take it out and report back, so that I can look at it. I can't do much without seeing the data and layout. Once you do that, please be specific about where you'd like me to look. Regular client work is piled up right now, so I may not respond immediately.
    – Erik Tyler
    Commented Jan 9, 2021 at 8:35
  • 1
    I took a look and approached it an entirely different way (now that I can see data in context). I've deleted your Column B header and all formulas from Column B, replacing them with one formula in cell B1, which uses MMULT and generates the header and all column results. Since my original reply and formula addressed the issue as presented in the original post here, I won't explain this second formula here. But you should be all set. I encourage you to take it apart and study it if you want to know the "how."
    – Erik Tyler
    Commented Jan 9, 2021 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.