# Re: sumif problems

1. ## 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. ## 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. ## 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. ## Re: sumif problems

Thank you again.

ed

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

#### 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