SumProduct compare columns a and b with columns e and f

thanks. i think that works..actually column e and f, may not be in exact same order as column a and f, so what u have works if both columns are in same order, but thats not case with large data points.

i was using with sumproduct

=SUMPRODUCT(--(A$2:A$5=E2),--(B$2:B$5=F2),A$2:A$5)


if they dont match show column a and column b respectively.

do u know whats wrong with sumproduct fucntion above returning all 0's

please see updated attached sheet

Hi Vegas,
SUMPRODUCT does just that; it sums by line/row the product of each of your arguments.

Your first two arguments

Please Login or Register  to view this content.

will return either a 1 or 0 depending on whether it is true for that row. Your third argument

Please Login or Register  to view this content.

should be either a 3rd true/false statement (to count instances where rows meet the criteria of all 3 arguments) or numeric values(for summing values in that column that meet the criteria of arguments 1 and 2). In your case, it is neither so you get zeros.

Does that help?

thanks chemistb, in actual spreadsheet im using

=SUMPRODUCT(--(A$2:A$5827=E2),--(B$2:B$5827)=F2,A$2:A$5827))

and getting 0 like u said, but i m not getting desired result

basically im trying to find "difference" where both sets of column dont match then display result from column a

SUMPRODUCT isn't made to do that. It won't look line by line and return the value in A for that line if your criteria is met. BigBias formula does that.