+ Reply to Thread
Results 1 to 8 of 8

Sumproduct for alternate rows with multiple columns

  1. #1
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Sumproduct for alternate rows with multiple columns

    Hi,

    I have a dataset with two alternating rows "Rate" and "Quantity" over 10 columns.
    I need to multiply Rate*Quantity for each set of rows for all columns and get the sum for the whole dataset

    I need a solution to avoid multiple SUMPRODUCTs

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Sumproduct for alternate rows with multiple columns

    Hi and welcome
    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Re: Sumproduct for alternate rows with multiple columns

    Hi,

    Thanks for the reply..
    Attached reference file
    However original dataset has 100s of rows so multiple SUMPRODUCT solution is tiring& prone to errors..
    Attached Files Attached Files

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Sumproduct for alternate rows with multiple columns

    a single SUMPRODUCT should suffice:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    edit, and if you prefer to avoid explicit coercion on the product ranges themselves:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 02-13-2019 at 06:27 AM.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Sumproduct for alternate rows with multiple columns

    Try this:

    =SUMPRODUCT((C2:K9)*(C3:K10)*(MOD(ROW(C2:K9),2)=0))

    Hope this helps.

    Pete

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sumproduct for alternate rows with multiple columns

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Re: Sumproduct for alternate rows with multiple columns

    Hi gurus,

    Thanks for the prompt response.
    All solutions work perfectly
    May I know which of these solutions would be using least resources ( the file is already a bit heavy)

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Sumproduct for alternate rows with multiple columns

    they will all perform similarly, indeed some of them are simply repetitions of each other...

    used in isolation, with reasonable dimensions, and with non-volatile precedents, there should be an immaterial impact on performance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumproduct Across Columns and Rows with multiple criteria
    By NainaH in forum Excel General
    Replies: 7
    Last Post: 01-31-2019, 11:26 AM
  2. [SOLVED] Sumif or Sumproduct with Multiple Rows & Columns
    By lqorri in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2017, 09:05 AM
  3. Sumif or Sumproduct with Multiple Rows & Columns
    By vulches in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2017, 02:06 AM
  4. [SOLVED] Alternate rows sumproduct
    By spidolster in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-03-2016, 07:39 PM
  5. [SOLVED] Sumproduct for multiple criteria across columns and rows
    By yacatac in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-12-2015, 07:46 AM
  6. Sumproduct Multiple Worksheets, Columns and Rows
    By PeterW in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-06-2007, 10:02 PM
  7. Sum of alternate columns & rows
    By kumawat_s in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2007, 10:59 AM

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