calculating

[tofimoon4]

My dear, please tell me ( with example) how to calculate values between
two dates .

 

[Sandy Mann]

Speaking for myself, I am not sure what it is you are asking. Why don't YOU
give US an example for what you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[tofimoon4]

Speaking for myself, I am not sure what it is you are asking. Why don'
YOU
give US an example for what you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

my dear, i mean if i have invoices with several dates like below :
1/5/2007 700
1/12/2007 1250
1/26/2007 750
2/6/2007 2000
2/15/2007 1850
2/25/2007 3200
3/1/2007 7500
and i need to calculate these values every 15 days or monthly

 

[Sandy Mann]

Monthly Totals:

January:
=SUMPRODUCT((MONTH(A1:A7)=1)*(B1:B7))

February:
=SUMPRODUCT((MONTH(A1:A7)=2)*(B1:B7))

March:
=SUMPRODUCT((MONTH(A1:A7)=3)*(B1:B7))

You don't say where the 15 days start so assuming that they start on 1/1/07:

1st 15 days:
=SUMPRODUCT((A1:A7>=--"1/1/07")*(A1:A7<=--"15/1/07")*B1:B7)

Next 15 days:
=SUMPRODUCT((A1:A7>--"15/1/07")*(A1:A7<=--"30/1/07")*B1:B7)

Etc.

That is Larger than (">") equals ("=") then a double unary ("--")to change
the text into a date.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

I forgot to change the dates in the 15 day formulas from my British style
into American style.

It would be better to place the date in cells - say 1/1/07 in C1, 1/15/07 in
C2, 1/30/07 in C3 etc. then the formulas would be:

1st 15 days:

=SUMPRODUCT((A1:A7>=C1)*(A1:A7<=C2)*B1:B7)

2nd 15 days:
=SUMPRODUCT((A1:A7>C2)*(A1:A7<=C3)*B1:B7)

No need for the confusing double unary.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Monthly Totals:

January:
=SUMPRODUCT((MONTH(A1:A7)=1)*(B1:B7))

February:
=SUMPRODUCT((MONTH(A1:A7)=2)*(B1:B7))

March:
=SUMPRODUCT((MONTH(A1:A7)=3)*(B1:B7))

You don't say where the 15 days start so assuming that they start on
1/1/07:

1st 15 days:
=SUMPRODUCT((A1:A7>=--"1/1/07")*(A1:A7<=--"15/1/07")*B1:B7)

Next 15 days:
=SUMPRODUCT((A1:A7>--"15/1/07")*(A1:A7<=--"30/1/07")*B1:B7)

Etc.

That is Larger than (">") equals ("=") then a double unary ("--")to change
the text into a date.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk