ARRAY FORMULA FOR TOTAL

[Maurizio]

1.xls
	A	B	C	D	E	F	G
1	10101	2		COD	TOTAL
2	10102	2		10000	9
3	10201	2		20000	12
4	10202	3		30000	14
5	20101	4		40000	18
6	20201	4		50000	24
7	20202	4
8	30110	6		WHATARRAYFORMULAISNESESSARY
9	30112	8		FORTOTAL?
10	40101	6
11	40102	6
12	41001	6
13	50101	8
14	50201	8
15	50301	8
Chiusura (2)

 

[IML]

could you just use a non array like:
=SUM(E2:E6) or
=SUM(B:B)
?

 

[paliman]

You don't need an array formula. Add a 99999999999 in D7 below the 50000. Then, in E2 enter and copy down:

=SUMPRODUCT(($A$1:$A$15>=$D1)*($A$1:$A$15<$D2)*$B$1:$B$15)

(Sorry, I'm having trouble with Colo's uyility)

 

[Mark W.]

You don't need SUMPRODUCT either... if you're willing to format column A values as Text and use...

=SUMIF($A$1:$A$15,LEFT(D2)&"*",$B$1:$B$15)

If column A contain account or department numbers if would be better to enter them as Text anyway because they won't be used directly in arithmetic operations.
This message was edited by Mark W. on 2002-10-21 15:53

 

[Maurizio]

Unfortunately column A contain ONLY NUMBER.
Other helps? Thanks.

 

[Aladin Akyurek]

On 2002-10-22 07:21, maurizio.rota wrote:
Unfortunately column A contain ONLY NUMBER.
Other helps? Thanks.

Maurizio,

It's:

=SUMPRODUCT((LEFT($A$1:$A$15)=LEFT(D2))+0,$B$1:$B$15)

Aladin

 

[arunjhalani]

R U using Analysis Tool Pack for this result ?
I think This is the Outcome of Histogram in the Analysis Tool Pack. R u just Trying to Validate the Outcome ?

Is The Summary is No off or Frequency ?

On 2002-10-21 15:22, maurizio.rota wrote:

1.xls
	A	B	C	D	E	F	G
1	10101	2		COD	TOTAL
2	10102	2		10000	9
3	10201	2		20000	12
4	10202	3		30000	14
5	20101	4		40000	18
6	20201	4		50000	24
7	20202	4
8	30110	6		WHATARRAYFORMULAISNESESSARY
9	30112	8		FORTOTAL?
10	40101	6
11	40102	6
12	41001	6
13	50101	8
14	50201	8
15	50301	8
Chiusura (2)

This message was edited by arunjhalani on 2002-10-22 08:02

 

[Mark W.]

On 2002-10-22 07:21, maurizio.rota wrote:
Unfortunately column A contain ONLY NUMBER.
Other helps? Thanks.

Right! I recommend that you change them to text if you're not going to use them in arithmetic expressions.

 

[Dave Patton]

or just use Sumif

=SUMIF($A$1:$A$20,">="&D2,$B$1:$B$20)-SUMIF($A$1:$A$20,">="&D3,$B$1:$B$20)

 

[Maurizio]

Thank you to everybody!