Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can sumproduct do two {x,y,z} arguments?
I am trying to use something like the following:
a. =SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$ 465={1,2})) which returns #N/A It works with this one: b. =SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$ 465={1})) ... but of course will not give me the same # since it's not counting 2s in AT. So sumproduct does not seem to like dealing with more than one array enclosed in {} .. at least the way I've tried it. I know I can use *((AT3:AT465=1) + (AT3:AT465=2)) but I would like to know if there's a way to do it in the style of formula a. above., or other style that's shorter than AT3:AT465 =1 + AT3:AT465=2 + etc.. thanks Dave R. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can sumproduct do two {x,y,z} arguments?
Try...
=SUMPRODUCT(--ISNUMBER(MATCH($AF$3:$AF$465,{1,2,6,12},0)),--ISNUMBER(MATC H($AT$3:$AT$465,{1,2},0))) Hope this helps! In article .com, wrote: I am trying to use something like the following: a. =SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$ 465={1,2})) which returns #N/A It works with this one: b. =SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$ 465={1})) .. but of course will not give me the same # since it's not counting 2s in AT. So sumproduct does not seem to like dealing with more than one array enclosed in {} .. at least the way I've tried it. I know I can use *((AT3:AT465=1) + (AT3:AT465=2)) but I would like to know if there's a way to do it in the style of formula a. above., or other style that's shorter than AT3:AT465 =1 + AT3:AT465=2 + etc.. thanks Dave R. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can sumproduct do two {x,y,z} arguments?
Try
=SUMPRODUCT(--ISNUMBER(MATCH(Range1,{1,2,6,12},0)),--ISNUMBER(MATCH(Range2,{1,2},0))) change range accordingly -- Regards, Peo Sjoblom http://nwexcelsolutions.com wrote in message oups.com... I am trying to use something like the following: a. =SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$ 465={1,2})) which returns #N/A It works with this one: b. =SUMPRODUCT(($AF$3:$AF$465={1,2,6,12})*($AT$3:$AT$ 465={1})) .. but of course will not give me the same # since it's not counting 2s in AT. So sumproduct does not seem to like dealing with more than one array enclosed in {} .. at least the way I've tried it. I know I can use *((AT3:AT465=1) + (AT3:AT465=2)) but I would like to know if there's a way to do it in the style of formula a. above., or other style that's shorter than AT3:AT465 =1 + AT3:AT465=2 + etc.. thanks Dave R. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can sumproduct do two {x,y,z} arguments?
....or the array formula
=SUMPRODUCT(MMULT(--(range1=bin1),TRANSPOSE(COLUMN(bin1)^0)), MMULT(--(range2=bin2),TRANSPOSE(COLUMN(bin2)^0))) bin1={1,2,6,12} bin2={1,2} ....with apologies to Harlan Grove. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |