+ Reply to Thread
Results 1 to 4 of 4

Re: sumif problems

  1. #1
    vezerid
    Guest

    Re: sumif problems

    #1:
    =SUMPRODUCT(--($A$2:$A$10<>"S"),D2:D10)

    #2:
    =SUMPRODUCT(--($A$2:$A$10<>"S"),D2:D10+E2:E10)

    HTH
    Kostis Vezerides


  2. #2
    edcosoft@sbcglobal.net
    Guest

    Re: sumif problems

    Kostis: Thank's. It works. Due to the different sizes of my
    various ranges I decided to use my formula in each column only, and
    then add a =Sum(D11:F11) in row 12.

    I see I left a comma out of my formula in my OP.

    However, you use <>"S" in your SUMPRODUCT formulas and if I try to
    change my formula in SUMIF to <>"S" I get an error message. If I try
    to change your SUMPRODUCT formula to "<>S" I get an error message. Why
    is this? I got my "<>S" straight from a SUMIF example in my 97 text.

    Also, what is the signifigance of the -- in your formula? It doesn't
    work without it and only one produces a negative value?

    thanks, ed


  3. #3
    vezerid
    Guest

    Re: sumif problems

    Ed,

    The typical, most frequent usage of SUMIF is for exact values, i.e.
    something like:

    =SUMIF(A1:A10, "S", D1:D10) ----if the value is text, it is enclosed
    in quotes.

    SUMIF and COUNTIF also accept criteria with simple inequalities. The
    inequality operators are part of the criterion. Even if the criterion
    is numeric, with inequality it must be enclosed in quotes. For example,
    the following two formulas will return complements of the total sum
    based on whether A1:A10 is 5 or not:

    =SUMIF(A1:A10, 5, D1:D10)
    =SUMIF(A1:A10, "<>5", D1:D10)

    SUMPRODUCT is a more powerful form. It multiplies pairwise arrays and
    produces the final sum. Thus, in the following,

    =SUMPRODUCT(--($A$2:$A$10<>"S"),D2:D10)

    we are multiplying the array D2:D10 with a virtual (computed) array of
    0's and 1's. The expression

    ($A$2:$A$10<>"S")

    returns True or False. The -- is to convert it to numbers through
    coersion.
    Since T/F is compatible with arithmetic, -True = -1 and --True = 1

    SUMPRODUCT could work with a single array argument, in which you make
    pairwise multiplication:

    =SUMPRODUCT(($A$2:$A$10<>"S")*D2:D10)

    Notice there is no comma now, but multiplication. The -- is not needed
    because multiplication forces coersion. As to the reasons your variants
    are not working:

    =SUMIF(A1:A10, <>"S", D1:D10)

    The problem here is syntactic. Between the commas we expect an
    expression returning a single value. <>"S" is not a valid expression.
    Whereas "<>S" is a single text literal, which is appropriately
    interpreted by SUMIF logic. In the other variant you tried:

    =SUMPRODUCT(--($A$2:$A$10"<>S"),D2:D10)

    the problem is again syntactic. An expression like A5"kkk" is illegal.
    In any other context except for SUMIF, anything within quotes loses its
    significance and counts only as text literal. This is why you are
    getting the error.

    HTH
    Kostis


  4. #4
    edcosoft@sbcglobal.net
    Guest

    Re: sumif problems

    Thank you again.

    ed


+ 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