Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array advance sum formula
=Sum(((Range1=value1/cell1)+(Range1=value2/cell2))*(Range2)
The above formula works fine it will sum Range2 where values in Range1 are matching with cell1/value1 OR cell2/value. My question is what if i have to compare values in Range1 with 10 or more cells/values. currently it works by repeating the conditions say 10 times, but this will make formula much bigger. Is there any way Range1 can be compared with cell1, cell2,...cell10 at the same time. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array advance sum formula
Try using an array constant if it is a list of values:
=Sumproduct((Range1={value1,value2,...})*Range2) Sumproduct has the advantage that it does not need to be array- entered. For cell references, instead of the array you can use a range such as A1:E1 or transpose(a1:a5) for a vertical range or Choose({1,2,3},b2,c5,e7) for a discontinuous range. On Feb 7, 1:32 pm, Amir Rasheed <Amir wrote: =Sum(((Range1=value1/cell1)+(Range1=value2/cell2))*(Range2) The above formula works fine it will sum Range2 where values in Range1 are matching with cell1/value1 OR cell2/value. My question is what if i have to compare values in Range1 with 10 or more cells/values. currently it works by repeating the conditions say 10 times, but this will make formula much bigger. Is there any way Range1 can be compared with cell1, cell2,...cell10 at the same time. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array advance sum formula
=SUM((ISNUMBER(MATCH(Range1,cell1:celln,0)))*Range 2)
and =SUM((ISNUMBER(MATCH(Range1,{"value1","value2"},0) ))*Range2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Amir Rasheed" <Amir wrote in message ... =Sum(((Range1=value1/cell1)+(Range1=value2/cell2))*(Range2) The above formula works fine it will sum Range2 where values in Range1 are matching with cell1/value1 OR cell2/value. My question is what if i have to compare values in Range1 with 10 or more cells/values. currently it works by repeating the conditions say 10 times, but this will make formula much bigger. Is there any way Range1 can be compared with cell1, cell2,...cell10 at the same time. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array advance sum formula
I would use SUMPRODUCT
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://mcgimpsey.com/excel/formulae/doubleneg.html For example, one can use =SUMPRODUCT(--(A1:A100=C1:E1)*B1:B100) to sum B1:B100 when corresponding A values match values in C1:E1 (must be horizontal range, as I recall) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Amir Rasheed" <Amir wrote in message ... =Sum(((Range1=value1/cell1)+(Range1=value2/cell2))*(Range2) The above formula works fine it will sum Range2 where values in Range1 are matching with cell1/value1 OR cell2/value. My question is what if i have to compare values in Range1 with 10 or more cells/values. currently it works by repeating the conditions say 10 times, but this will make formula much bigger. Is there any way Range1 can be compared with cell1, cell2,...cell10 at the same time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |