+ Reply to Thread
Results 1 to 7 of 7

Using SUMPRODUCT (no SUMIFS) to summarize mapped data

  1. #1
    Registered User
    Join Date
    05-10-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    3

    Question Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    Hi all,

    Please see my attached image to go along with my question. I'm trying to accomplish this without SUMIFs, as they require another workbook to be open. I am aware of how simply this could be done with SUMIFs, but I would like to do it using SUMPRODUCT or some other method.

    I have a set of 1) Available Information in one workbook, which lists sales of products by years.
    I have a 2) Mapping of products to product types.
    I would like to produce a 3) Final Product that sums up the total sales of product types for each given year.

    Is there some way for me to accomplish this using SUMPRODUCTS or another way? I've been racking my brain for hours.

    SUMPRODUCT.PNG

    Thanks so much!

    Edit: added spreadsheet
    Attached Files Attached Files
    Last edited by LMB1991; 05-10-2021 at 07:45 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Registered User
    Join Date
    05-10-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    I have fixed this.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    In C28

    =SUMPRODUCT(($B$4:$B$12={"A","B","C"})*(INDEX($C$4:$G$12,,MATCH(C$27,$C$3:$G$3,0))))

    Copy across

    Change "A","B","C" to appropriate group for Beta & gamma
    Attached Files Attached Files
    Last edited by JohnTopley; 05-10-2021 at 07:58 AM.

  5. #5
    Registered User
    Join Date
    05-10-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    Thank you, however this is unsuitable, as in reality there are hundreds of products.

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    what is the sumif formula that doesn't work? it should be able to be changed into a sumproduct

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

    Try


    =SUMPRODUCT(($B$4:$B$12=$B$16:$B$24)*($C$16:$C$24=$B28)*(INDEX($C$4:$G$12,,MATCH(C$27,$C$3:$G$3,0))))
    Attached Files Attached Files

+ 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] vlookup() with sumifs() to summarize between two numbers?
    By desertsp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2018, 02:04 PM
  2. [SOLVED] Help with SUMIFS or SUMPRODUCT
    By Dan_Ludwig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2015, 09:35 AM
  3. [SOLVED] SUMIFS or SUMPRODUCT???
    By PERE in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-25-2013, 11:52 AM
  4. [SOLVED] SUMIFS to SUMPRODUCT
    By plsm5882 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-31-2013, 05:30 PM
  5. [SOLVED] SUMIFS to SUMPRODUCT
    By JungleJme in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-13-2012, 12:14 PM
  6. [SOLVED] Summarize data with count ifs/sumifs or similar
    By bertrand82 in forum Excel General
    Replies: 1
    Last Post: 05-21-2012, 06:58 AM
  7. Replies: 0
    Last Post: 10-07-2010, 10:37 AM

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