+ Reply to Thread
Results 1 to 7 of 7

SUMIFS with multiple conditions Formula

  1. 02-09-2015, 01:59 PM #1
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    SUMIFS with multiple conditions Formula

    I have a SUMIFS formula (in highlighted yellow in attached file) and is summing based on criteria. However, my formula is not adding up as I want to. I think it's because I want it to sum two criteria in the same column (Fixed Bid or T&M or both)- it could one or the other or both. If the formula would work correctly, it would total $769,279 for all DEPTS... what am I doing wrong?

    Formula: =SUMIFS('V Dump'!AB:AB,'V Dump'!$D:$D,DEPTS!$D$2,'V Dump'!$N:$N,DEPTS!$G$13,'V Dump'!$K:$K,DEPTS!$D31,'V Dump'!$M:$M,DEPTS!$D28,'V Dump'!$M:$M,DEPTS!$E28)

    Refer to attached sample file -
    Attached Files Attached Files
    Last edited by rz6657; 02-09-2015 at 02:52 PM.
    Reply With QuoteRegister To Reply
  2. 02-09-2015, 02:16 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: SUMIFS and / or

    I would solve this with an pivot table.

    See the attached file.

    Notice I added new values in the row 95 - 100 to also have other values in the pivot table.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 02-09-2015, 02:17 PM #3
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS and / or

    You have a few problems there...

    Formula in G31
    =SUMIFS('V Dump'!AB:AB,'V Dump'!$D:$D,DEPTS!$D$2,'V Dump'!$N:$N,DEPTS!$G$13,'V Dump'!$K:$K,DEPTS!$D31,'V Dump'!$M:$M,DEPTS!$D28,'V Dump'!$M:$M,DEPTS!$E28)

    The main problem you're trying to do is summing on 2 criteria in the same column as an OR...
    If column M = D28 OR E28
    By default, sumifs is an AND criteria, it's testing each cell in column M, and will only count/sum it if that cell = D28 AND that cell = E28
    Which is impossible for 1 cell to = 2 different values..

    To do an OR like this in SUMIFS, you have to do
    =SUMPRODUCT(SUMIFS('V Dump'!AB:AB,'V Dump'!$D:$D,DEPTS!$D$2,'V Dump'!$N:$N,DEPTS!$G$13,'V Dump'!$K:$K,DEPTS!$D31,'V Dump'!$M:$M,DEPTS!$D28:$E28)))

    Now, the next problem is that you're testing if column N = G13.
    But G13 is BLANK.
    Looks like that should be changed to G15

    Finally, Still testing if column N = G15.
    Column N is just numbers, they're not really dates.
    G15 is just a Text String "Jan"
    So NONE of the values in column N = "Jan"


    You're going to need to clarify what the numbers in column N are representing.
    You have 42014 52014 62014
    Are those supposed to be April2014 May2014 June2014 etc?

    Would you be trying to count Jan of ANY year, or specifically 2014 ?
    Reply With QuoteRegister To Reply
  4. 02-09-2015, 02:30 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: SUMIFS and / or

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 02-09-2015, 02:39 PM #5
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: SUMIFS and / or

    Jonmo1 -

    But G13 is BLANK. - actually it's not blank just made font to white so it wasn't visible. I've made font red so you can see. Also, I added your formula and it's not working. I've attached TEST FILE2...

    Thanks...
    Reply With QuoteRegister To Reply
  6. 02-09-2015, 02:51 PM #6
    rz6657
    rz6657 is offline
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: SUMIFS with multiple conditions Formula

    Never mind. It works. Thanks.
    Reply With QuoteRegister To Reply
  7. 02-09-2015, 03:13 PM #7
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS with multiple conditions Formula

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Looking for something simpler than SUMIFS - SUMIFS
    By YAbdelaal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-27-2014, 10:38 AM
  2. Sumifs
    By pickslides in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2014, 01:34 AM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  5. [SOLVED] IF and/or SUMIFS
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-12-2012, 09:12 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1