+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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...
    Attached Files Attached Files
    Last edited by tpillow; 08-06-2009 at 09:04 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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

  3. #3
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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.

  5. #5
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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.
    Attached Files Attached Files
    Last edited by tpillow; 08-06-2009 at 03:50 AM.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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.

  7. #7
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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...

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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)

  9. #9
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1