1. Hi All

I currently have 6 data blocks.
I am using a '7th' block to sum the data in these 6 data blocks.

One of my formulas is:
=BH67 + DH67 + FH67 + HH67 + JH67 + LH67

I now want to insert another 4 data blocks.
Which means I need to add another 4 terms in my sample formula.

I don't want to use a custom function.
Is there a more efficient way of writing this formula?
Or do I simple add my required 4 extra terms?

If I have a cell which specifies the number of data blocks I have (e.g. a value from 1 to 10), could I use a more efficient formula??

zeddy

2. For the consistent pattern here you could use

=SUMPRODUCT((BH67:TH67)*(MOD(COLUMN(BH67:TH67),52) =8))

This works because the Column Numbers being added always give 8 when used with MOD 52

so Column BH is column 60 and 60 MOD 52 =8
Column DH is column 112 and 112 MOD 52 is 8 etc

The SumProduct Function then adds all the cells in the range where the MOD 52 of the Column Number is 8

You can extend this idea over other ranges, IF the pattern is consistent.

3. Hi Andrew

Brilliant!!
Thumbs up from me.
Thanks for reminding me about SumProduct.
However, I'm getting a circular ref error which I'm now trying to trace the cause.
Will get back to you.

Many thanks again

zeddy

4. Hi Andrew

Sorted the circ ref issue.

So, with the marvellous formula you suggested, I do indeed get my answer.

Second question now relates to calc performace.
I have estimated that I may need to 'copy' your sumproduct formula to approx 20,000 cells.
From your experience, would I be better to use the elegant sumproduct formula, or go with the longer =a+b+c+..+j

zeddy

5. Without testing I would not know for sure, but I suspect that the SUMPRODUCT formula
would calculate slower than the simple addition, because it has rather more do do.
Hopefully someone can come up with the definitive answer.

6. Many thanks Andrew

When I get more time, I'll test both methods.

zeddy

7. SUMPRODUCT will be slower. Do you have any sort of header row that you could use in a SUMIF formula?

8. Hi Rory

Re: One of my formulas is:
=BH67 + DH67 + FH67 + HH67 + JH67 + LH67..

This formula will be copied across a span of 36-columns, and then 'down' in several 'blocks' (typically 4 or 5 rows per block) , through several hundred rows.

I do have a few header rows available though.

zeddy

9. What I mean is, is there an identiying header row for those columns? E.g. if you had "Data1" in cells BH1, DH1, FH1 etc. then you could use a SUMIF fomula using row 1 as the criteria.

10. Aha!

Yes, these 'blocks' span 36-months and each will have a mmm-yyyy header.

zeddy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•