MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I sum in EXCEL


Posted by Manu on March 12, 2001 7:31 AM

Here's what I am up against...

I have 2 columns . Column 1 has dates (1998, 1999, 2000 etc..) and the 2nd column has numbers for each date.

So for e.g. there are 10 fields with date = 1998 and each date has a number associated with it in column 2.

My dilemma is how do I add up the numbers from column 2 for same dates. In other words how do I get a total for all numbers(from column 2) belonging to 1998.

Any help would be much appreciated.

Thanx

Manu


Posted by Mark W. on March 12, 2001 7:35 AM

Manu, I recommend that you use a PivotTable.

Posted by Aladin Akyurek on March 12, 2001 7:46 AM

Assume years are in A1:20 on and numbers in B1:B20.

Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time) the following formula:

=SUM((A1:A20=1998)*(B1:B20))

You can also select the cells with years in them and name the range YEARS and the cells with numbers to sum, e.g., DATA via the Name Box.

Then you can array-enter:

=SUM((YEARS=1998)*DATA)

Aladin

Posted by Manu on March 12, 2001 8:21 AM


Hi Aladin

I tried what you suggested but all I am getting as an answer to the array is 0. May I not doing something correctly. Here's the steps that I took

1. Named the range - Years
2. Named the data - Data

entered the array in an empty cell: sum((years=1998)*data)

and then hit enter

all I got was 0

Any comments??

Thanx for your time

Manu

Posted by Aladin Akyurek on March 12, 2001 8:44 AM

Manu

To enter an array formula you need to hit CONTROL+SHIFT+ENTER keys at the same time instead of just ENTER. When you do it right, you see { } appear around the formula on the formula bar.

Aladin