Copying a formula down relative by a group of cells.

[sillston]

Hello,

I have a list of products and their availability across 5 branches (grouped vertically by product). Is there a way to copy a formula down so it is relative by 5 rows instead of changing for each row?
I've currently used absolute cell references to sum the total of each product but manually entering this for each product is going to be a lengthy process i.e if product A is row 2-6 and product B 7-11, I want to copy the SUM formula for rows 2-6 to 7-11, 12-16 and so on. Is this possible or another way of achieving this?

Example attached.

Thanks in advance.

[humdingaling]

hmmm, the way i would handle this is to

would be to do each set of formulas (do not use absolute references) then copy and paste and then copy the range and copy and paste, etc, etc
so it goes from 1,2,4,8,16,32,64
then more you have the more efficient it becomes over time

ie the formula in m2 becomes

Please Login or Register  to view this content.

m3

Please Login or Register  to view this content.

...m6

Please Login or Register  to view this content.

copy m2:M6 paste into m7
copy m2:m11 paste into m12
Copy m2:m21 paste into m22
etc

this said you dont have to do each column individual
fix up all the formulas in column K to M to NOT be absolute but individually referenced ranges

then you can effectively do
K2:m6 copy paste into K7
K2:M11 copy paste into K12
etc

[humdingaling]

ALTERNATIVE to this
as it seems like you have "unique" CODE & Colour combinations you can apply sumifs based on column F
so formula in k2

Please Login or Register  to view this content.

filldown
same sort of deal for column L

column M would require MAXIF (or maxifs)
which i believe is a formula from Excel 2016 onwards

**
https://support.office.com/en-us/art...b-9b6376b28883
Note: This feature is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

if you dont have this like i dont
then an array formula can be used to replace this
https://exceljet.net/formula/maximum-value-if

[sillston]

That's great, thanks for you help. I've used a combination of your two answers which seems to have worked so far.

[humdingaling]

not a problem at all

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.