+ Reply to Thread
Results 1 to 2 of 2

Sumif or sumproduct with multiple criteria

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    N Carolina
    MS-Off Ver
    Excel 2003
    Posts
    1

    Question Sumif or sumproduct with multiple criteria

    In the attached file I have successfully written formulas in row 19 using sumif & sumproduct that will display the total budget (calculated from column L) when filtered. In columns M & N, it accurately gives me the budget total of projects that have any mark in those columns).
    For example, when I filter on company 0006 (column B), it shows that of the total project budget of $1,116,000, $250,000 were closed, and $29,000 had a date change.

    My next challenge has stumped me though. I want to write formulas for cells M21-N26 that will give me those numbers by company, WITHOUT filtering the file. In otherwords, I want the company subtotal/results to be visible at all times, whether the file is filtered or not. I know it will need to look at both the contents of column B & either column M or N, and return the sum of column L, but I don't know how to write the formulas. Please help me

    Thanks in advance!

    (BTW, I have inserted L29-N34 manually. By filtering the file by company, I know that these are the values that should be the result of the formula I'm searching for).
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Sumif or sumproduct with multiple criteria

    You can use SUMIFS.

    =SUMIF(SUM RANGE, CRITERIA RANGE1, CRITERIA1, CRITERIA RANGE 2, CRITERIA2, ETC)

    M =SUMIFS(L$3:L$17,$B$3:$B$17,MID($J21,3,4),M3:M17,"x")

    and

    N =SUMIFS(L$3:L$17,$B$3:$B$17,MID($J21,3,4),N3:N17,"x")

+ 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/sumproduct with a range as a criteria
    By fukirua in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-05-2013, 07:58 PM
  2. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  3. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 AM
  4. SUMIF Multiple Criteria or SUMPRODUCT?
    By gdwright07 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2008, 12:15 PM
  5. [SOLVED] sumif or sumproduct with date as criteria
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-02-2005, 12:59 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