If you want more info:
Chip Pearson has notes for array formulas at:
http://www.cpearson.com/excel/array.htm
(and =sumproduct() is another way of entering an array formula)
And you may want to get a copy of Bob Umlas's white paper:
http://www.emailoffice.com/excel/arrays-bobumlas.html
The formula is essentially doing:
for each cell in RngA return true/false (if it matched sheet2!b1)
For each cell in rngC return true/false (if it matched)
If the ranges are small, you can highlight that portion of the formula and hit
F9 to see how it evaluates:
Highlight "rnga=sheet2!b1" and hit F9 and you'll see a bunch of true/falses.
Now the product part of sumproduct says to multiply those true/falses against
the corresponding true/false.
(true*true evaluates to 1, anything else T*F, F*T, F*F evaluates to 0.)
Then you have a sequence of 1/0's for those two portions of the formula.
When you multiply the 1/0's by the values in rngD, you'll get the value in each
cell in rngD if it was multiplied by a 1 or 0 if it was multiplied by the 0.
Then the sum portion of sumproduct adds up all those individual pieces: