software

#1:
=SUMPRODUCT(--($A$2:$A$10lt;gt;quot;Squot;),D210)

#2:
=SUMPRODUCT(--($A$2:$A$10lt;gt;quot;Squot;),D210 E2:E10)

HTH
Kostis VezeridesKostis: 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 lt;gt;quot;Squot; in your SUMPRODUCT formulas and if I try to
change my formula in SUMIF to lt;gt;quot;Squot; I get an error message. If I try
to change your SUMPRODUCT formula to quot;lt;gt;Squot; I get an error message. Why
is this? I got my quot;lt;gt;Squot; 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, edEd,

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

=SUMIF(A1:A10, quot;Squot;, D110) ----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, D110)
=SUMIF(A1:A10, quot;lt;gt;5quot;, D110)

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

=SUMPRODUCT(--($A$2:$A$10lt;gt;quot;Squot;),D210)

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

($A$2:$A$10lt;gt;quot;Squot;)

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$10lt;gt;quot;Squot;)*D210)

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, lt;gt;quot;Squot;, D110)

The problem here is syntactic. Between the commas we expect an
expression returning a single value. lt;gt;quot;Squot; is not a valid expression.
Whereas quot;lt;gt;Squot; is a single text literal, which is appropriately
interpreted by SUMIF logic. In the other variant you tried:

=SUMPRODUCT(--($A$2:$A$10quot;lt;gt;Squot;),D210)

the problem is again syntactic. An expression like A5quot;kkkquot; 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
KostisThank you again.

ed