Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Three same Formulas
Hi everyone
I've got these three sumproduct formulas that returns the right answer. =SUMPRODUCT((YEAR(A5:A150)=2008)*(C5:C150="Dept021 ")*B5:B150) =SUMPRODUCT(--(YEAR(A5:A150)=2008),--(C5:C150="Dept021"),B5:B150) =SUMPRODUCT(N(YEAR(A5:A150)=2008),N(C5:C150="Dept0 21"),B5:B150) I would like to know if they're all the same. Can they be use for different situation. Any preference over others and for what reason? Thank you Regards John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Three same Formulas
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
-- __________________________________ HTH Bob "John" wrote in message ... Hi everyone I've got these three sumproduct formulas that returns the right answer. =SUMPRODUCT((YEAR(A5:A150)=2008)*(C5:C150="Dept021 ")*B5:B150) =SUMPRODUCT(--(YEAR(A5:A150)=2008),--(C5:C150="Dept021"),B5:B150) =SUMPRODUCT(N(YEAR(A5:A150)=2008),N(C5:C150="Dept0 21"),B5:B150) I would like to know if they're all the same. Can they be use for different situation. Any preference over others and for what reason? Thank you Regards John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Three same Formulas
On Jan 9, 11:32*am, "John" wrote:
Hi everyone I've got these three sumproduct formulas that returns the right answer. =SUMPRODUCT((YEAR(A5:A150)=2008)*(C5:C150="Dept021 ")*B5:B150) =SUMPRODUCT(--(YEAR(A5:A150)=2008),--(C5:C150="Dept021"),B5:B150) =SUMPRODUCT(N(YEAR(A5:A150)=2008),N(C5:C150="Dept0 21"),B5:B150) I would like to know if they're all the same. Can they be use for different situation. Any preference over others and for what reason? Thank you Regards John They are all the same. You need to use an operator to get SUMPRODUCT to see your logical expressions with 1s and 0s instead of TRUEs and FALSEs. Those formulas all do that in a different way. The most common seems to be the "--", probably because it is distinctive and versatile. Pick which you like and always use it so it will be familiar. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |