+ Reply to Thread
Results 1 to 3 of 3

COUNTIF & SUM with conditions

  1. #1
    Registered User
    Join Date
    01-27-2008
    Posts
    11

    COUNTIF & SUM with conditions

    Hello. Looking for help. Searched forums and didn't find exactly what I am looking for. Here is what I am trying to accomplish:
    - Column A contains list of three names in repetitive order: John, Bob, Mary. The range in column A is A6:A20
    - Column B contains $ amount associated with each name. For example: A6 = John, B6 = $100; A7 = Bob, B7 = $200; etc
    - Column C contains the status of whether bill was paid by John, Bob, etc. For example: A6 = John, B6 = $100, C6 = "paid"; A7 = Bob, B7 = $200, C7 = "pending"
    - Then, there are three cells with running totals for John, Bob and Mary, A3, B3, C3. As columns A and B are getting populated, the running total for corresponding name is reflecting the total current sum paid. The only caveat is a "running total" cell should only add to the total sum if the status in column C shows "paid" and if it shows "pending", then the amount pending shouldn't be part of total sum.

    Hope it all makes sense. An example is attached. I would appreciate your help.
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: COUNTIF & SUM with conditions

    Hello,

    in Excel 2003 and earlier: Cell A3

    =SUMPRODUCT(($A$6:$A$20=A2)*($C$6:$C$20="paid")*$B$6:$B$20)

    copy across

    In Excel 2007 and later

    =SUMIFS($B$6:$B$20,$A$6:$A$20,A2,$C$6:$C$20,"paid")

    Please update your profile to reflect your Excel version.

  3. #3
    Registered User
    Join Date
    01-27-2008
    Posts
    11

    Re: COUNTIF & SUM with conditions

    Thank you very much for such quick reply! It worked beautifully!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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