Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    SUMIF & Concatenate (Excel 2002)

    Hi,
    I'm in need of a formula that will look at a large database and then sum the dollar amount base on 3 different variables. For example, I'd like to sum the dollar amounts if it is company "84", nature "Sales" and month "JAN07". I'm not wanting to use a pivot table (for various reasons) and currently I add another column in the database to concatenate the 3 variables and then do a sumif to add. Is there an easier formula... maybe the "match" formula?? I can't seem to get that one to work?!!!? Any ideas will be greatly appreciated. I've attached a worksheet with an example.
    Thanks!
    Lana
    Attached Files Attached Files

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

    Re: SUMIF & Concatenate (Excel 2002)

    For "SUMIF" formulas with multiple conditions, you can use SUMPRODUCT:
    <code>
    =SUMPRODUCT((A2:A7=84)*(B2:B7="Sales")*(C2:C7="JAN 07")*D27)
    </code>
    or if you put the values you want to filter on in (for example) L1, L2 and L3:
    <code>
    =SUMPRODUCT((A2:A7=L1)*(B2:B7=L2)*(C2:C7=L3)*D27)
    </code>
    Alternatively, you can use this array formula (confirm with Ctrl+Shift+Enter):
    <code>
    =SUM(IF((A2:A7=L1)*(B2:B7=L2)*(C2:C7=L3),D27))
    </code>
    The latter will also work with other aggregate functions such as MIN, MAX and AVERAGE.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: SUMIF & Concatenate (Excel 2002)

    Awesome...thanks Hans!
    Lana

Posting Permissions

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