Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: sum if

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have 3 fields.. branch, affiliate, and a number field (mf).

    i want to SUM mf IF branch=0001 AND affiliate="Balanced"

    How?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try SUMPRODUCT

    if branch is in column A starting at A2 and affiliate is in column B starting at B2 and mf is in column C starting at C2 :

    =SUMPRODUCT(($A$2:$A$100="0001")*($B$2:$B$100="Balanced"),($C$2:$C$100))

    I use this a lot now and prefer to put the actual variables in their own cells (0001 and "Balanced") so I can just link to them in case I need to change them...... rather than changing the actual formula 100 (or more) times :

    =SUMPRODUCT(($A$2:$A$100=F1)*($B$2:$B$100=G1),($C$2:$C$100))

    where F1 houses "0001" and G1 houses "Balanced"



    ie


    :: Pharma Z - Family drugstore ::

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 10:41, kellshepherd wrote:
    I have 3 fields.. branch, affiliate, and a number field (mf).

    i want to SUM mf IF branch=0001 AND affiliate="Balanced"

    How?
    Use...

    {=SUM(mf*(branch="0001")*(affiliate="Balanced"))}

    ...where mf, branch and affiliate are cell ranges with the same dimensions.

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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