# ARRAY FORMULA FOR TOTAL

#### Maurizio

##### Well-known Member
1.xls
ABCDEFG
1101012CODTOTAL
2101022100009
31020122000012
41020233000014
52010144000018
62020145000024
7202024
8301106WHATARRAYFORMULAISNESESSARY
9301128FORTOTAL?
10401016
11401026
12410016
13501018
14502018
15503018
Chiusura (2)

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### IML

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

#### paliman

##### Active Member
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.

##### MrExcel MVP
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

##### Well-known Member
Unfortunately column A contain ONLY NUMBER.
Other helps? Thanks.

##### MrExcel MVP
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)

#### arunjhalani

##### Board Regular
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
ABCDEFG
1101012CODTOTAL
2101022100009
31020122000012
41020233000014
52010144000018
62020145000024
7202024
8301106WHATARRAYFORMULAISNESESSARY
9301128FORTOTAL?
10401016
11401026
12410016
13501018
14502018
15503018
Chiusura (2)
This message was edited by arunjhalani on 2002-10-22 08:02

#### Mark W.

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
Thank you to everybody!

Replies
6
Views
386
Replies
6
Views
420
Replies
5
Views
80
Replies
11
Views
304
Replies
12
Views
100

1,181,684
Messages
5,931,406
Members
436,788
Latest member
Oteez

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back