ARRAY FORMULA FOR TOTAL

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows
1.xls
ABCDEFG
1101012CODTOTAL
2101022100009
31020122000012
41020233000014
52010144000018
62020145000024
7202024
8301106WHATARRAYFORMULAISNESESSARY
9301128FORTOTAL?
10401016
11401026
12410016
13501018
14502018
15503018
Chiusura (2)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
could you just use a non array like:
=SUM(E2:E6) or
=SUM(B:B)
?
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
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
Joined
Feb 10, 2002
Messages
11,654
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
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Oct 8, 2002
Messages
121

ADVERTISEMENT

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
Joined
Feb 10, 2002
Messages
11,654
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
Joined
Feb 15, 2002
Messages
4,972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
or just use Sumif

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

Forum statistics

Threads
1,144,510
Messages
5,724,774
Members
422,576
Latest member
kayth891

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top