Help! Incrementing part of formula by nth when dragging/copying

[demelza]

Hello. I have the following formula -

=SUMIF('Budget Resort'!$B:$B,"Labour",'Budget Resort'!AO:AO)

I need to drag this formula across multiple columns in a row, and I need the "'Budget Resort'!AO:AO" part to increment by 3 columns rather than 1 every time. So each cell across the sheet will show the formula like this -

=SUMIF('Budget Resort'!$B:$B,"Labour",'Budget Resort'!AO:AO)
=SUMIF('Budget Resort'!$B:$B,"Labour",'Budget Resort'!AR:AR)
=SUMIF('Budget Resort'!$B:$B,"Labour",'Budget Resort'!AU:AU) .. etc

In 'Budget Resort' sheet there are Column headers that are split into 3 columns for each month, so the headings are "NZD, USD, Hours, NZD, USD, Hours... etc" and there are hundreds of row's that have different items I can put in the 'criteria part of the formula. I can't use filtering because this is going to be a tool I give Project Manager for forecasting so I need the results sheet to just give me the total figures required without any filtering.

I need to copy this hundreds of times and for different criteria, so adjusting the formula manually for each cell isn't an option. I'd really appreciate help on this! Thanks

 

[ukmikeb]

Hi

Welcome to the forum.

Try -

=SUMIF('Budget Resort'!$B:$B,"Labour",Offset('Budget Resort'!$AO:$AO,,(COLUMNS($AD:AD)-1)*3))

adjust $AD:AD to point to the cell where you enter the formula.

hth

 

[demelza]

Wow thank you so much Mike!!! That's worked perfectly! Thanks!

 

[ukmikeb]

Hi

Pleased to have helped solve your problem.

Thanks for the feedback.

Good luck with your project.