+ Reply to Thread
Results 1 to 14 of 14

I thought that SUMIF would work...

Hybrid View

  1. #1
    Registered User
    Join Date
    02-21-2005
    Posts
    10

    Question I thought that SUMIF would work...

    Okay, SUMIF isn't working for me, perhaps I'm entering the fields incorrectly...

    For example-

    --A---B---C
    1 10 30 $5
    2 20 30 $10
    3 30 30 $5
    4 40 10 $10
    5 50 20 $10

    Okay, Column B has a validation set as a list of Column A (so I can enter an new account in column A, and charge towards it as many times as I want in Column B by selecting the account from A using a drop-down)

    I want my formula to add C wherever A=B. So, for the above example, the result for line 3 would be $20 (A3=B1, B2, B3). Essentially, I want to have a running sum for whenever the account # in column A is charged.

    =SUMIF(A1:A5,"=B1:B5",C1:C5) isn't working. I'm blocking on the answer...
    Last edited by burgeon; 04-11-2005 at 10:21 AM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    The SUMIF that you are using matches a range against a single value.
    For instance, if you were looking for the sum of all items where Col A = 20, the you would use:
    =SUMIF(A1:A5,"=20",C1:C5)

    BUT: You want to calculate the sum for items where Col A items match Col B items. Since, you are comparing 2 ranges, so you need an array formula like this one:
    =SUM(($A$1:$A$5=$B$1:$B$5)*$C$1:$C$5)
    (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter])

    Does that help?

    Regards,
    Ron

  3. #3
    Registered User
    Join Date
    02-21-2005
    Posts
    10
    That code only seems to be working within a given line, that is if A1=B1 then add C1, almost like a true/false result. If I plug that formula into column D, thjisis the result I'm getting:

    --A---B---C--D
    1 10 30 $5 0
    2 20 30 $10 0
    3 30 30 $5 $5
    4 40 10 $10 0
    5 50 20 $10 0

    How do I get D# to return "$20" (i.e. C1+C2+C3)?

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    I'm confused. You enter the formula. Then you hold down Control and Shift when you press Enter. If you do that, you should see braces { } at the extreme ends of the formula. (Note: Don't type the braces in...Excel puts them in.)

    You do the above and you don't get the correct results?

    Also, please confirm: For each row where the value in column A matches the value in column B you want to add the values in Column C. Is that correct?


    Ron
    Last edited by Ron Coderre; 04-11-2005 at 11:34 AM.

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    OK...I think I'm FINALLY on the right track here.

    Try running this formula down col D:

    =IF(A1=B1,SUM(C$1:C1),0)
    Just press Enter for this one.

    Note though, it always sum from the C1 down to the row where A=B. Is that what you want?

    Regards,
    Ron

  6. #6
    Registered User
    Join Date
    02-21-2005
    Posts
    10
    Let me put it a different way - Column A is an account number. Columns B and C indicate charges toward the accounts in column A. So, every time I enter an account number from A in B, I want it to keep a running subtotal. Therefore, I charged "Account 30" three times in column B, twice for $5 and once for $10. I actually want to create a column that will give me a running total of charges next to each account number in column A. Does that make sense? Sorry, I'm hving trouble verbalizing it!

    BTW, after hitting ctrl, shift before hitting enter (giving me the brackets), in my example I'm getting:

    --A---B---C--D
    1 10 30 $5 $5
    2 20 30 $10 $5
    3 30 30 $5 $5
    4 40 10 $10 $5
    5 50 20 $10 $5

    When I want to get:

    --A---B---C--D
    1 10 30 $5 $5
    2 20 30 $10 $0
    3 30 30 $5 $20
    4 40 10 $10 $5
    5 50 20 $10 $0

    (Accounts 10 and 40 were charged $5 each, and Account 30 was charged $20. Accounts 20 and 50 weren't chrged anything - they don't appear in column B)

  7. #7
    Registered User
    Join Date
    02-21-2005
    Posts
    10
    OK...I think I'm FINALLY on the right track here.

    Try running this formula down col D:

    =IF(A1=B1,SUM(C$1:C1),0)
    Just press Enter for this one.

    Note though, it always sum from the C1 down to the row where A=B. Is that what you want?
    Almost! that gives me the result that I want for line 3, but I want it to say whenever there's a match in line B for A, add C...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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