Sum.if criteria related to range of cells
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! -- Desiree-Canary Islands |
Sum.if criteria related to range of cells
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 "Desiree" wrote: 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! -- Desiree-Canary Islands |
Sum.if criteria related to range of cells
Another variation that appears to work okay.
=SUMPRODUCT(SUMIF(B1:B5,F5:F10,C1:C5)) "Desiree" wrote: 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! -- Desiree-Canary Islands |
Sum.if criteria related to range of cells
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 "Desiree" wrote: 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! -- Desiree-Canary Islands |
All times are GMT +1. The time now is 09:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com