+ Reply to Thread
Results 1 to 7 of 7

Sumproduct with multiple conditions help

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Sumproduct with multiple conditions help

    Hey all need help with a sumproduct formula.

    I am doing my company's expense report. The expenses are broken down by the pay period the expense was paid, the amount, the code which the expense is under, the name of the code, and the department the expense goes to. (Attached document)

    For some reason my Sumproduct formula is not working I keep getting $0 when there is suppose to be another number

    =SUMPRODUCT(--($A$1:$A$14>=$G$2),--($A41:$A$14<=$G$3),--($C$1:$C$14="530"),--($C$1:$E$14="M"),--($B$1:$B$14))

    But when I use this other formula to get the total of all billable and all NonBillable It works

    =SUMPRODUCT(--($A$1:$A$14>=$G$2),--($A$1:$A$14<=$G$3),--(D1:$D$14="Billable")--($E$1:$E$14="M"),--(B1:B14))

    Does the formula not like specifc numbers to look for or is it something else?

    Thanks
    Attached Files Attached Files
    Last edited by Murphy15; 07-31-2013 at 03:22 PM.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Sumproduct with multiple conditions help

    Some minor amendments..try this

    =SUMPRODUCT(--($A$1:$A$14>=$G$2),--($A$1:$A14<=$G$3),--($C$1:$C$14=530),--($E$1:$E$14="M"),$B$1:$B$14)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Sumproduct with multiple conditions help

    Try Using 530 instead of "530"

    Cheers!

    Deep
    Cheers!
    Deep Dave

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sumproduct with multiple conditions help

    Hi,

    The third of these clauses is searching for a text entry of "530", and so will ignore the numerical entries in that column. Should be:

    =SUMPRODUCT(--($A$1:$A$14>=$G$2),--($A$1:$A$14<=$G$3),--($C$1:$C$14=530),--($E$1:$E$14="M"),--($B$1:$B$14))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumproduct with multiple conditions help

    Remove the quotes from "530" because those values are numeric not text, or better to use cell references like

    =SUMPRODUCT(--($A$1:$A$14>=$G$2),--($A$1:$A$14<=$G$3),--($C$1:$C$14=$I6),--($E$1:$E$14=J$4),$B$1:$B$14)
    Audere est facere

  6. #6
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Re: Sumproduct with multiple conditions help

    Thanks you SO MUCH!! Will make the thread solved

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Sumproduct with multiple conditions help

    Actually according to your table, this would be the perfect formula I guess

    =SUMPRODUCT(--($A$1:$A$14>=$G$2),--($A$1:$A$14<=$G$3),--($C$1:$C$14=$I5),--($E$1:$E$14=J$4),--($B$1:$B$14))

    And Just Drag either ways

    Deep

+ 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. Multiple (3) SUMPRODUCT conditions
    By timjames in forum Excel General
    Replies: 2
    Last Post: 11-03-2011, 03:17 PM
  2. Multiple conditions in one column for sumproduct.
    By einarai in forum Excel General
    Replies: 4
    Last Post: 08-07-2011, 04:01 AM
  3. Sumproduct - Using multiple conditions
    By EXCELNEWCOMER in forum Excel General
    Replies: 5
    Last Post: 02-11-2010, 04:43 PM
  4. Sumproduct - multiple conditions
    By rasonline in forum Excel General
    Replies: 10
    Last Post: 06-04-2009, 05:10 PM
  5. Sum with multiple conditions:SUMPRODUCT
    By will.00 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2007, 03:51 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