Urgent Urgent Urgent!!!
 

Dear All,
I have the following data:

Data Amounts

01.01.05 1000
02.01.05 2000
10.01.05 14000
01.02.05 3000
12.02.05 2500
.... etc. ..etc.

I need to calculate the sum of all amounts in january, february, march,
etc...separately....
in other words, ...if data is within january then sum of january's amounts
and so on...
I appreciate you help in advance,
Ruslan

Dates are not in date format..convert all date in to date format...then you
can put filter for respective months.

"Ruslan" wrote:

Dear All,
I have the following data:

Data Amounts

01.01.05 1000
02.01.05 2000
10.01.05 14000
01.02.05 3000
12.02.05 2500
... etc. ..etc.

I need to calculate the sum of all amounts in january, february, march,
etc...separately....
in other words, ...if data is within january then sum of january's amounts
and so on...
I appreciate you help in advance,
Ruslan

See Help for the SUMIF worksheet function.

Jerry

Ruslan wrote:

Dear All,
I have the following data:

Data Amounts

01.01.05 1000
02.01.05 2000
10.01.05 14000
01.02.05 3000
12.02.05 2500
... etc. ..etc.

I need to calculate the sum of all amounts in january, february, march,
etc...separately....
in other words, ...if data is within january then sum of january's amounts
and so on...
I appreciate you help in advance,
Ruslan

In my solution I assumed your data column is A1:A5, and the amounts are
in B1:B5. The dates are a question mark, tho: are they custom
formatted "Date" type data fields, or "Text" type data fields?

If they are dates, the formula is:
=SUMPRODUCT(--(MONTH(A1:A5)=1),B1:B5)
This is for January, month 1. You can create results for other months
by changing the month number in the formula.

If the dates are text, the formula is:
=SUMPRODUCT(--(MID(A1:A5,1,2)="01"),B1:B5)
This formula performs some text parsing; if you need to get results for
other months replace "01" with two digit months numbers.

Ruslan,

Use a Pivot table - Select your data, use Data | Pivot Table. Then drag
"Data" to the row filed area, and "Amounts" to the data area. Then on the
Pivot Table commandbar, select the pivot table dropdown, then choose "Group
and Show detail" then choose Group, and select the month option, and Excel
will automatically sum your data (or average, or show max, or min, etc.) by
month. No formulas. Ever.

HTH,
Bernie
MS Excel MVP

"Ruslan" wrote in message
...
Dear All,
I have the following data:

Data Amounts

01.01.05 1000
02.01.05 2000
10.01.05 14000
01.02.05 3000
12.02.05 2500
... etc. ..etc.

I need to calculate the sum of all amounts in january, february, march,
etc...separately....
in other words, ...if data is within january then sum of january's amounts
and so on...
I appreciate you help in advance,
Ruslan

Ruslan,

Using the Conditional Sum Wizard, I came up with the following array formula
(use Control+Shift+Enter to enter it):

{=SUM(IF(DateCol=DATEVALUE("01/01/2005"),IF(DateCol<=DATEVALUE("31/01/2005"),ValCol,0),0))}

(The curly brackets get added automatically when you press
control+shift+enter)
You can change the dates to make the formula give an answer for a particular
month in a particular year, as the SumProduct example shown elsewhere won't
distinguish between different years.

Now, if someone can show ME how to substute cell references containing start
and end dates for the "DATEVALUE" parts of the formula above, then I@D be
grateful, too!

Hope this helps

Pete









"Ruslan" wrote:

Dear All,
I have the following data:

Data Amounts

01.01.05 1000
02.01.05 2000
10.01.05 14000
01.02.05 3000
12.02.05 2500
... etc. ..etc.

I need to calculate the sum of all amounts in january, february, march,
etc...separately....
in other words, ...if data is within january then sum of january's amounts
and so on...
I appreciate you help in advance,
Ruslan

Ruslan,

Found it!

Assuming your dates are in a range "DateCol"
and the values are in a renge "ValCol"
your start date is cell H3
your end date is cell I3

the array formula you require is:

{=SUM(IF(DateCol=H3,IF(DateCol<=I3,ValCol,0),0))}

Remember to hold down Control+Shift while pressing Enter

You can then copy this formula to other cells to calculate totals for other
months.

Thanks for the challenge!

Pete




"Ruslan" wrote:

Dear All,
I have the following data:

Data Amounts

01.01.05 1000
02.01.05 2000
10.01.05 14000
01.02.05 3000
12.02.05 2500
... etc. ..etc.

I need to calculate the sum of all amounts in january, february, march,
etc...separately....
in other words, ...if data is within january then sum of january's amounts
and so on...
I appreciate you help in advance,
Ruslan