Sum.if criteria related to range of cells

[Guest]

Hi all,
first sorry about my english (I'm Spanish native).
My question: I would like to use the function sum.if but in the criteria I
would like to use something like this: sum.if(B1:B5;"="f5:f10;c1:c5)
I know that I can use sum.if(b1:b5;"="&f1;c1:c5), but how to do it with a
range of cells? I have seen that you recomend the function "sumproduct" for
this kind of questions but in the Spanish Excel 2007 this function
"sumaproducto" it does not allow to use criteria (it just add numbers of a
matrix). Does anybody know if the translation of "sumproduct" is other
function?
If you can think in any other way for getting the data and not writing all
the numbers, please let me know.
Thanks a lot!

 

[Guest]

Try this:

I assume you want to match B1:B5 with F5:F10 and then sum C1:C5 (?)

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B5,F5:F10,0))),C1:C5)

HTH

 

[Guest]

Another variation that appears to work okay.

=SUMPRODUCT(SUMIF(B1:B5,F5:F10,C1:C5))

 

[Guest]

Here is a good link for more info on sumproduct (Bob has not yet translated
it into Spanish, but your English is pretty good so you may not have much
trouble).

http://xldynamic.com/source/xld.SUMPRODUCT.html