sumif problems

[edcosoft]

I have two problems:
#1. If I write a sumif formula =SUMIF($A$2:$A$10, "<>S" D210) it
does not include any items that are not coded in A at all, let alone
the required S to elimiate addition of rows coded S. That is, if cell
A for that row is blank it treats it like S and doesnt' include it in
the sum.

So, how do I make sure the formua works if there IS a blank cell in
colum A?

#2. Worse yet, I am trying to sum in a range D2:E10 so my formula is
=sumif($A$2:$A$10,"<>S",D2:E10). That is a range more than one column
wide. My result is only the sum in column D and does not include
column E numbers. What am I doint wrong and how do I achive my
desired result to sum the entire range D2:E10 (except for column coding
S)?

TYIA ed

 

[vezerid]

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

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

HTH
Kostis Vezerides

 

[edcosoft]

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

 

[vezerid]

Ed,

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

=SUMIF(A1:A10, "S", 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, "<>5", D110)

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"),D210)

we are multiplying the array D210 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")*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, <>"S", D110)

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"),D210)

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

 

[edcosoft]

Thank you again.

ed