Thread: Multiply every nth cell by another column whith merged cells, THEN find column total

Hi - I know my thread title may be suboptimal, so once we zero in on which formulas to use, I will add those keywords.

Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".

In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.

If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.

Thanks in advance...

Your first problem is that you are using merged cells in Column A which will preclude you from using certain formulae... never (ever) use Merged Cells ... most developers would argue they shouldn't even exist in XL as they add no value and severely complicate functionality.

For the sake of demo I will show how you can achieve your result once merged cells are dispensed with, ie let's insert a new column in your file after A (such that Schedule now appears in C1 rather than B1), then:

B2: =IF(A2,A2,B1)
copied down

Then

C30: =SUMPRODUCT((MOD(ROW($B$2:$B$29)-ROW($B2)+1,7)=0)*(C$2:C$29)*($B$2:$B$29))
copied across

No, you should have the value in EVERY cell in the block, if you wish use Conditional Formatting to ensure only the middle value is highlighted, or as already illustrated use a further column (hidden) which stores the values in each cell accordingly which you can reference in calcs in preference to the merged area.

General rule of thumb: if you require the cell in a calculation be it for reference, summation etc etc do not merge.

OK.

I changed the sheet according to you reccomendations, and my calculations are now achieving the expected results. I will now try to find a way to make it work without duplicating my data 7x - in all 7 cells within each block of 7 cells - throughout column A. One reason for that would be to allow for easy totaling of column A. Another reason is simply presentational. Two competing solutions are, in my view:

a) Conditional formating to make data in all but the fourth cell in each block of cells in column A invisible, then summing every 7th cell in the column.
b) Deleting the data from each block of cells in column A except for the 4th cell, the isolating the 4th cell in the calculations using some kind of IF statement.

I like A because it avoids VBA - not mandatory in this case but IMO generally preferable from a usability standpoint. For reference, the new sheet is attached. I'll let you know if I fix it.

As I said you need the value in every row of the matrix, ie 2:29 ... and you should then alter reference to $A$5 to $A$2 accordingly.

Given your blocks repeat in an even fashion why not simply highlight A2:A8 & set Font to White then subsequently highlight A5 set font to black, copy the format of A2:A8 over the remaining rows in A ?

To sum the quantity:

=SUMPRODUCT(--(MOD(ROW(A2:A29),7)=4),A2:A29)