i want to understand the difference Between Range And Array

[MyExcel]

Hi all
i want to understand the difference between array and range
i feel this is misleading
thanks in advanced

 

[jbeaucaire]

To my mind, they are similar. They are both a series of values or potential values.

But a Range is series physically located in cells somewhere, and an array is completely in computer memory....an array needs to be "written out" somewhere when you're done or you may lose it, which may be fine depending on what you used the array for.

 

[MyExcel]

Thanx
but it still not clear for me
see here in this post what RichardSchollar say about array in Post number 5
http://www.mrexcel.com/forum/showthread.php?t=401194

SUMIFs doesn't take arrays

But Sumproduct take Array
i understand how Sumproduct and sumifs work
but i can't Distinguish between them in Array and Range

 

[Colin Legg]

It's due to how the functions are registered.

SUMPRODUCT() expects arrays. If you pass a reference to a range into SUMPRODUCT, it is clever enough to extract the values of that range (an array of values).

So, example:

Excel Workbook
	A	B	C	D
1	1	5
2	2	6
3	3	7
4	4	8		70
Sheet1

Excel 2002

Cell Formulas
Range	Formula
D4	=SUMPRODUCT(A1:A4,B1:B4)

We have passed references to ranges (A1:A4 and B1:B4) into SUMPRODUCT. But really the ranges are dereferenced into arrays {1;2;3;4}, like so:

=SUMPRODUCT(A1:A4,B1:B4)

becomes --->

=SUMPRODUCT({1;2;3;4}, {5;6;7;8})


A1:A4 and B1:B4 are ranges.
{1;2;3;4} and {5;6;7;8} are arrays.

Does that help?

 

[MyExcel]

Excel Workbook
	A	B	C	D
1	Date	Customers	Region	Amount
2	01/01/2008	ABC	East	12584
3	31/03/2008	CDE	East	16651
4	29/06/2008	ACC	West	24199
5	27/09/2008	DAD	East	23692
6	26/12/2008	AEA	West	24320
7	26/03/2009	ANA	East	18305
8	24/06/2009	ACC	East	19356
9	30/06/2009	ACC	West	25197
10	01/07/2009	ACC	East	16336
11	06/07/2009	ABC	East	12597
12
13	35692
14	35692
Thanx colin L Sheet1

Excel 2007

Cell Formulas
Range	Formula
A13	=SUMPRODUCT(--(YEAR($A$2:$A$11)=2009),--($B$2:$B$11="ACC"),--(C2:C11="East"),D2:D11)
A14	=SUMIFS($D$2:$D$11,$A$2:$A$11,">=2009-01-01",$A$2:$A$11,"<2010-01-01",$B$2:$B$11,"ACC",$C$2:$C$11,"East")

bt i this example
Sumproduct Accept to use formula to convert date and then sum it because it is array case
but SumIfs Doesn't becuse it is Range
what i understand from it
when i am useing formula like Sumproduce i can use another formula into it to do onther job
while
we can't do it in SumIFs

 

[MyExcel]

 

[Aladin Akyurek]

See Colin's description. We would to add that (A) some functions like Offset, SumIf, CountIf, SumIfs, and CountIfs are designed to operate only on (multi-cell) range objects, while (B) Sum, SumProduct, Frequency, Linest, lookup functions, etc. take both range and array objects.

Consider A2:A5...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64 align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>5</TD></TR></TBODY></TABLE>

which is a range object.

Try:

1. SUMIF(A2:A5,">3")

2. SUMIF({3;2;4;5},">3")

The formula in (2) won't succeed, which figures an array object (i.e., {3;2;4;5}) as reference.

3. FREQUENCY(A2:A5,2)

4. FREQUENCY({3;2;4;5},2)

Both (3) and (4) would happily yield the same result.

BTW:

YEAR($A$2:$A$11)

yields an array object that SumProduct can process, while SumIf(s) by design as stipulated above cannot.

 

[MyExcel]

See Colin's description. We would to add that (A) some functions like Offset, SumIf, CountIf, SumIfs, and CountIfs are designed to operate only on (multi-cell) range objects, while (B) Sum, SumProduct, Frequency, Linest, lookup functions, etc. take both range and array objects.

thanx Aladin
i read Colin's Desription, and it is great Tips and 1000 thanx for him
but

when i am useing formula like Sumproduce i can use another formula into it to do onther job
while
we can't do it in SumIFs

this is a case which puzzled me ?

 

[Aladin Akyurek]

thanx Aladin
i read Colin's Desription, and it is great Tips and 1000 thanx for him
but

this is a case which puzzled me ?

See my BTW note in my last reply...

 

[MyExcel]

Sorry i write my reply before u edite yours