Thread: sumproduct
View Single Post
  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

anand wrote:
I can create a sumproduct formula that refers to external cells for a
filtering function
e.g. =sumproduct(A1:A200=A205:D205)*(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.

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"? So if I want a
formula that indicates, for example everyhing with a "apple" in column one
but not a "orange" in column 2?


=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$200,$A$205:$D$205,0)),--($B$1:$B$200=1))