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

1. ## 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.  Register To Reply

2. ## Re: Multiply every nth cell by another column, THEN find column total

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  Register To Reply

3. ## Re: Multiply every nth cell by another column, THEN find column total

I've found merged cells useful for display purposes with information that is not numeric and not involved in any calculations, but I guess I understand your basic point....

But so in this case, instead of using merged cells I should put the "quantity" in the middle cell of each merged segment, and then blank the borders on each block of cells, to mimic the appearance of merged cells without costing me necessary functionality?  Register To Reply

4. ## Re: Multiply every nth cell by another column, THEN find column total

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.  Register To Reply

5. ## Re: Multiply every nth cell by another column, THEN find column total

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.  Register To Reply

6. ## Re: Multiply every nth cell by another column, THEN find column total

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.  Register To Reply

7. ## Re: Multiply every nth cell by another column, THEN find column total

Yeah I made that post without seeing your later post. I was in the process of editing it accordingly. That post (and the sheet) are now completely different. God willing, there's a way to cleanse column A of all the repetition, but you give the impression that there isn't, aside from hiding it using conditional formating...  Register To Reply

8. ## Re: Multiply every nth cell by another column, THEN find column total

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)  Register To Reply

9. ## Re: Multiply every nth cell by another column, THEN find column total

I noticed your formula targets the bottom cell (7th) in each column A block, so I just deleted the other 6 and everything seems to work fine. I would give feedback but it's asking me to "spread the love" for awhile first...

Thanks for that, from now on I'll think twice before merging cells!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 