Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Sumif not adding values correcly

    I have values in Columns B to D and ageing of the data in Col E

    I have tried to set up a SUMIF formulas to compute the following

    1) The value of the data in Columns B to D where the ageing is 7 days or less
    2) The value of the data in Columns B to D where the ageing is greater than 7 days

    My formula only gives the total of the values in Col B


    =SUMIF(E5:E46,"<=7",B5 to D46) B646 does not show in formula on post

    =SUMIF(E5:E46,">7",B5 to D46)

    If sumif can't be used, please advise how I can use Sumproduct to do this for me

    Your assistance will be most appreciated
    Last edited by HowardC; 2013-04-09 at 11:03.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    Posting a sample file would make it easier.

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Attached please find file as requested

    Regards

    Howard
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    This works:
    =SUMIF(E5:E34,">7",B5:B34)+SUMIF(E5:E34,">7",C5:C3 4)+SUMIF(E5:E34,">7",D5:D34)

    As you have it, it is only summing col B. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Feb 2013
    Posts
    20
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I think you need to use a matrix or table function. I have never been able to use those except for 1 column at a time.

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, much appreciated. Would sumproduct not be easier to use in this instance and if so how do I use this formula?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =sumproduct((e5:e46<=7)*b5: D46)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    Why not simplify it as per attached file?

    I changed some Ageing values in rows 5 to 12 to show some results in rows 44 and 46
    (Original sample values are copied in column [H] )

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-04-09)

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help

    Good idea to simply and show ageing per category i.e new used wholesale

    With regard ro the sumproduct, I had the formula almost correct i.e =SUMPRODUCT(E5:E34>7)*B534. This returned # Value

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •