+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT of multiple columns with multiple criteria over multiple sheets

  1. #1
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    567

    SUMPRODUCT of multiple columns with multiple criteria over multiple sheets

    I've got 2 sheets, 1 with a load of numerical scores on it, the other with information about the target.

    I need to be able to total up all scores (cols E to J on scores sheet) for a particular target (name in Col C of the info sheet) with various criteria....eg Col D on scores sheet must equal "S" (there's lots more criteria).

    I can do this with nested SUMIFS, eg:

    Please Login or Register  to view this content.
    but calculation takes ages...

    And the Excel help file on SUMPRODUCT isn't that helpful. Could someone point me in the right direction pls?

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,820

    Re: SUMPRODUCT of multiple columns with multiple criteria over multiple sheets

    Please post a sample file, showing expected results, so we can better understand what are trying to do.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT of multiple columns with multiple criteria over multiple sheets

    Quote Originally Posted by BellyGas View Post
    And the Excel help file on SUMPRODUCT isn't that helpful.
    Here's a link to info on SUMPRODUCT:

    http://xldynamic.com/source/xld.SUMPRODUCT.html

    One thing of note, you should avoid using entire columns as range references with the SUMPRODUCT function. Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    567

    Re: SUMPRODUCT of multiple columns with multiple criteria over multiple sheets

    Disregard, solved it in Oracle before the data gets to Excel.

+ 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. Replies: 1
    Last Post: 08-16-2015, 08:59 AM
  2. Count w/multiple criteria across multiple columns - SUMPRODUCT, MATCH?
    By MrHoohah in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2015, 05:23 PM
  3. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  4. Multiple cells, multiple sheets, multiple criteria matching
    By Drudnits1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2014, 12:12 PM
  5. Replies: 1
    Last Post: 02-19-2014, 03:07 AM
  6. [SOLVED] Counting multiple items with multiple criteria from multiple sheets?
    By essee in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-23-2013, 01:56 AM
  7. Replies: 2
    Last Post: 01-23-2013, 06:25 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