Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add text to begining of cells within a range based on specified criteria | Excel Discussion (Misc queries) | |||
Range related for chart | Excel Discussion (Misc queries) | |||
color a range of cells based on a criteria | Excel Discussion (Misc queries) | |||
move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
Search a worksheet and add related cells | Excel Worksheet Functions |