SImple SUMIF

[mas_lerdanch]

Hey
My third post in this excellent forum! :->

I want to sum my column 11 (Named Profit2007) when column 1 (Named Date2007) is equal to any given month eg June or 06.

The date range in Date2007 runs from 01/10/2006.

Date2007 is formatted 01/01/2007.

Is there also a formula to give cumulative results week by week starting on that date?

Thanks in Advance

All the best Mas Lerdanch

 

[dave3009]

Hi Mas

Try SUMPRODUCT

=SUMPRODUCT(--(MONTH($A$2:$A$600)=6), $K$2:$K$600)

Hope it helps

KR


Dave

 

[mas_lerdanch]

Thanks Dave that works well and I can adapt it to suit.

My booking year starts in September and ends in September how do I specify a year and month instead of just a month?

best wishes

Mas

 

[Aladin Akyurek]

Hey
My third post in this excellent forum! :->

I want to sum my column 11 (Named Profit2007) when column 1 (Named Date2007) is equal to any given month eg June or 06.

The date range in Date2007 runs from 01/10/2006.

Date2007 is formatted 01/01/2007.

Is there also a formula to give cumulative results week by week starting on that date?

Thanks in Advance

All the best Mas Lerdanch

X2 houses a relevant from date, Y2 a relevant to date, with Y2 >= X2...

=SUMIF(Date2007,">="&X2,Profit2007)-SUMIF(Date2007,">"&Y2,Profit2007)

 

[Stormseed]

Thanks Dave that works well and I can adapt it to suit.

My booking year starts in September and ends in September how do I specify a year and month instead of just a month?

best wishes

Mas

Here you go...

Book1
	A	B	C	D
1	Date	Formula	Values
2	3/3/2007	30	10
3	6/3/2005		20
4	6/9/2006		30
5
Sheet1

 

[mas_lerdanch]

Thanks Aladin and Stormseed! will crack on! All the best - Mas

 

[mas_lerdanch]

Dear Aladin -

From your formula for a specific week I am getting a return of 0.

I have tried adapting but to no avail ... am i being silly...

example.xls
	A	B	C	D	E	F	G
1
2		Datex	Adhoc	Profit	Turnoverx
3		10/10/2005	0.00	1,460.00	1,460.00		(SUMIF(Datex,">="&1/1/2007,Turnx))-(SUMIF(Datex,">"&8/1/2007,Turnx))
4		12/10/2005	0.00	675.00	675.00		0
5		12/10/2005	0.00	1,540.00	1,540.00
6		12/10/2005	0.00	990.00	990.00
7		12/10/2005	0.00	710.00	710.00
8		13/10/2005	0.00	930.00	930.00
9		14/10/2005	0.00	432.00	432.00
10		14/10/2005	0.00	1,340.00	1,340.00
11		14/10/2005	0.00	1,702.86	1,702.86
12		17/10/2005	0.00	780.00	780.00
13		18/10/2005	0.00	3,500.00	3,500.00
14		18/10/2005	0.00	385.71	385.71
15		19/10/2005	0.00	2,142.86	2,142.86
16		19/10/2005	0.00	375.00	375.00
17		20/10/2005	0.00	664.29	664.29
18		20/10/2005	0.00	550.00	550.00
19		22/10/2005	0.00	770.00	770.00
20		22/10/2005	0.00	758.57	758.57
Sheet1

 

[mas_lerdanch]

The amp; is not in the formula or gt is not in the formula
the formula i am using is as follows - sorry first time using Colo's Html Generator...

Formula
=(SUMIF(Datex,">="&13/10/2005,Turnx))-(SUMIF(Datex,">"&20/10/2005,Turnx))

 

[mas_lerdanch]

Woo hoo - a little tweaking got me a result - i can't see why the other wasn't working though...

=SUMIF(Datex,"<="&DATE(2005,10,20),Turnx)-SUMIF(Datex,"<"&DATE(2005,10,13),Turnx)