"anand" wrote in message

...

I can create a sumproduct formula that refers to external cells for a

filtering function

e.g. =sumproduct(A1:A200=A205205)*(B1:B200=1).
That should be

=SUMPRODUCT((A1:A200=A205

205)*(B1:B200=1))

this should return the sum of those # of rows where there is a 1 in row B

and any of 4 different values (which I can switch from text or numbers) in

the first 4 cells of row 205.

Question is how can I indicate either blanks or nonblanks in one of the 4

reference cells in row 205.
What exactly do you mean? It works with blanks in A1:A200, or in A205

205

In addition, what does the operator "-" do when replacing the asterisk (*)

in the sumproduct formula. I know that a plus sign means "or" rather than

"and" for the asterisk. does the "-" symbol mean "but not"?
No, the - goes with another, a double unary, which is used to coerce the

True/False results of a test are coerced to 1/0 values, for the arithmetic

operations.

So if I want a

formula that indicates, for example everyhing with a "apple" in column one

but not a "orange" in column 2?
No, that is a simple <

=SUMPRODUCT(--(A1:A200="apple),--(B1:B200<"orange"))

equivalent to

=SUMPRODUCT((A1:A200="apple)*(B1:B200<"orange"))