Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sumif multi-conditions in same column (Excel 2000>)

    Hi,
    Is there a shorter way to sum multiple values in 1 column. I need a single value that will be returned which will be referenced by other calc's!
    In the example I may need to calc the sum of salaries (dummy data!) based on several departments. I have seen examples of using SumProduct to calc conditions in 2 or more columns etc, or using arrays. Is there a shorter easier way to do this, other than adding several sumif's???
    TIA for any advice!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif multi-conditions in same column (Excel 2000>)

    You could use this array formula (confirm with Ctrl+Shift+Enter):

    =SUM(IF((D222="Art")+(D222="Admin."),F2:F22))

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumif multi-conditions in same column (Excel 2000>)

    Another approach is with the DSUM function, but this requires that a criteria range be created...

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sumif multi-conditions in same column (Excel 2000>)

    Thanx Steve and Hans. The array function is what I was after, but thanx for reminding me of the DSUM function Steve. I must admit that I forgot about those DFunctions. This will also come in handy with additional criteria that could be used!
    Cheers
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumif multi-conditions in same column (Excel 2000>)

    The d-functions are probably one of the faster ways to setup with multiple criteria both ANDs and ORs. It does not require a temp column, nor use the resources that array functions use.

    Steve

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    A Sumproduct formula,

    =SUMPRODUCT((D222={"Art","Admin"})*(F2:F22))

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    When I try it, it only gives the SUM of the "Art"s. Is there some "trick" that I am missing? I even tried entering as an array (ctrl-shift-enter).

    Steve

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    No, no "tricks"

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>My mistake. Rudi's admins have a period which your formula did not...

    Steve

  10. #10
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    Actually it's mine, I didn't even notice the .

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    I didn't see it when I tested it, I just assumed it only saw the first one...

    Steve

  12. #12
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    Taking this one step further, if you do have multiple criterias, you can also use

    =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A100,B1:B10,0))),D1100)

    Where B1:B10 houses criteiras. Easier to maintain, but not any faster!

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sumif multi-conditions in same column (Excel 2

    Thanx Maxflia, I'll archive this one too. I'm always interested in how you can use multiple ways to get to the same answer. Thats why I posted this question, as I knew there must be a better way of setting up the function and improving my original function!
    Thanx
    Regards,
    Rudi

  14. #14
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif multi-conditions in same column (Excel 2

    It's faster.
    Microsoft MVP - Excel

  15. #15
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif multi-conditions in same column (Excel 2000>)

    Agree, in particular when a multiconditional summing is required. But, if the formula needs to be copied to a multitude of cells, the speed advantage decreases steadily for one needs to use the table method which boils down to an array formula entered in multiple cells. See my contrib in:

    http://www.mrexcel.com/board2/viewtopic.php?t=60895
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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