COUTIFS Formula help needed

TanyaMac

Hi, I am trying to write a formula that will pick the right data to automatically fill the Actuals - Monthly cell (Row 11 in the description column). I have tried an INDEX MATCH formula (=INDEX([M1 2025/26 (£000's)]2:[M12 2026/27 (£000's)]2, MATCH(CurrentMonth2, [M1 2025/26 (£000's)]1:[M12 2026/27 (£000's)]1, 0)) and kept getting error messages and I have tried COUNTIFS formula (=COUNT([M1 2025/26 (£000's)]29:[M12 2026/27 (£000's)]29, MONTH([M1 2025/26 (£000's)]1:[M12 2026/27 (£000's)]1) = CurrentMonth2)) but it will only count the amount in the cell if it is the same as the month (month 1 = 1). I have attached the Smartsheets sheet I am using in excel format so you can see what I am working with. The cells I am using are columns M1 2025/26 (£000's) - M12 2026/27 (£000's). The months have been written below in rows 1 and 2 with and formula calculating the actual month according to our financial year in CurrentMonth row 2. The actuals will go into row 29 under the relevant month.

Thanks so much

Project Finance Sheet.xlsx

prime_nathaniel

@TanyaMac

For horizontal, you can use a giant series of if thens to change the column refernce by month. You should note that aggregate formulas sum, count, avg, etc all work vertically not horizontally as column names are not variables within the context of a sheet.

If your source data is vertical, you can just calculate that via cross references.

TanyaMac

Thanks for your help, are you able to suggest an example given the info I shared?