=ROUND((SUMPRODUCT(SUMIFS(Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],Import_Balance_Sheet_I_and_E[NOM_CODE],U11:DZ11))),0)
The above formula works great, never any issues, however, there are thousands of them and as such it's slow. I'm hoping there is an alternative to having to specify the cell range U11:DZ11 and instead of saying that the last cell is DZ11, it would only go as far as the first blank cell. For instance in the above formula range U11:DZ11, only the first three columns are populated, ie. only U11 & V11 & W11 contain any data, all columns after that are blank, so ideally it would say U11:W11. But on the row below it, the first 75 columns are in use. So i'm effectively using a catch all range of U11:DZ11 forumla as i don't want to have to go through thousands of rows adjusting the U11:DZ11 to the specific number of columns, as this would take several days to achieve.
I was wondering if the Offset function would work, i'm utterly useless with Offset i hasten to add, despite reading many websites on how to use. A colleague suggested (i think) Index and Match, but again i have no idea how to use those functions either.
Any help appreciated.
The above formula works great, never any issues, however, there are thousands of them and as such it's slow. I'm hoping there is an alternative to having to specify the cell range U11:DZ11 and instead of saying that the last cell is DZ11, it would only go as far as the first blank cell. For instance in the above formula range U11:DZ11, only the first three columns are populated, ie. only U11 & V11 & W11 contain any data, all columns after that are blank, so ideally it would say U11:W11. But on the row below it, the first 75 columns are in use. So i'm effectively using a catch all range of U11:DZ11 forumla as i don't want to have to go through thousands of rows adjusting the U11:DZ11 to the specific number of columns, as this would take several days to achieve.
I was wondering if the Offset function would work, i'm utterly useless with Offset i hasten to add, despite reading many websites on how to use. A colleague suggested (i think) Index and Match, but again i have no idea how to use those functions either.
Any help appreciated.