+ Reply to Thread
Results 1 to 3 of 3

Combine sumif/sumproduct or sumproduct with multiple criteria

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    11

    Combine sumif/sumproduct or sumproduct with multiple criteria

    Dear All

    I have a table where I need to build sumproducts, but only if 2 criteria are met.
    The formula using one criteria only is:
    =Sumproduct(B:B=B2)+0;K:K;H:H)

    Column B: years (sum if year equal)
    Column c: company name (2nd criteria: in equal years sum if company is eaqual)
    Column K: amount
    Column h: percentage

    Of all the entries, I want to multiply K*H for each entry and display the total across all entries with same year/company

    Hope this makes sense

    Best regards
    Sandra
    Attached Images Attached Images

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Combine sumif/sumproduct or sumproduct with multiple criteria

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Combine sumif/sumproduct or sumproduct with multiple criteria

    =Sumproduct(--(B:B=B2);--(C:C=C2);K:K;H:H)
    it is not efficient to link the whole column

+ 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] Sumif or Sumproduct with multiple criteria ??
    By Sultix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2018, 04:51 PM
  2. [SOLVED] SUMIF/SUMPRODUCT Multiple Criteria
    By melissanelson110 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-01-2017, 02:20 PM
  3. SUMIF or SUMPRODUCT formula to SUM totals based on multiple criteria
    By relmasri in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-01-2016, 01:08 PM
  4. [SOLVED] Sumif or Sumproduct with multiple criteria
    By BPSJACK in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-30-2015, 06:25 AM
  5. [SOLVED] index match and sumproduct or sumif on multiple criteria
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 06:20 AM
  6. Sumif or sumproduct with multiple criteria
    By msoregon91 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 03:56 PM
  7. SUMIF Multiple Criteria or SUMPRODUCT?
    By gdwright07 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2008, 12:15 PM

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