+ Reply to Thread
Results 1 to 4 of 4

sumif non-number not equal to cell X

  1. #1
    Registered User
    Join Date
    08-07-2007
    Posts
    2

    sumif non-number not equal to cell X

    Object:
    To add amounts in column A, where the corresponding row-cell in column B is not equal to cell B1, a non-number (i.e. "a").

    When I put the value in the formula:
    =SUMIF(B:B,"<>a",A:A)
    it works.

    But I need the cell number in the formula, so I can change the value in B1, and not have to change the formula.
    But this:
    =SUMIF(B:B,"<>B1",A:A)
    does not work.

    Any suggestions?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try instead:

    =SUMIF(B:B,"<>"&B1,A:A)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-07-2007
    Posts
    2
    It works, thank you!

    Can you explain?
    Does the ampersand (&) indicate that what follows is a a cell reference?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The & concatenates the 2 values... It is because I don't have the B1 in quotes that it takes B1 as a reference cell.

+ 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