I'd be grateful for your assistance please. I think I should know how to do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates, April
2003, May 2003 and so on. The cells actually contain 01/04/03, 01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D) and a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice Values on
the Solutions sheet where the Invoice month and year match the month and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I can't
quite get my head round it.

I appreciate your help, thanks

Trevor

this should help.
=sumproduct((year(daterng=year(a1))*(month(daterng =month(a1))*rngtosum)

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
I'd be grateful for your assistance please. I think I should know how to
do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates, April
2003, May 2003 and so on. The cells actually contain 01/04/03, 01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D) and
a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice Values on
the Solutions sheet where the Invoice month and year match the month and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I
can't
quite get my head round it.

I appreciate your help, thanks

Trevor

Believe Don meant
sumproduct((year(daterng)=year(a1))*(month(daterng )=month(a1))*rngtosum)

Regards,
Tom Ogilvy



Don Guillett wrote in message
...
this should help.
=sumproduct((year(daterng=year(a1))*(month(daterng =month(a1))*rngtosum)

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
I'd be grateful for your assistance please. I think I should know how
to
do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates, April
2003, May 2003 and so on. The cells actually contain 01/04/03,
01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D)
and
a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice Values
on
the Solutions sheet where the Invoice month and year match the month and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I
can't
quite get my head round it.

I appreciate your help, thanks

Trevor

We all forget a ) once in awhile.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Tom Ogilvy" wrote in message
...
Believe Don meant
sumproduct((year(daterng)=year(a1))*(month(daterng )=month(a1))*rngtosum)

Regards,
Tom Ogilvy



Don Guillett wrote in message
...
this should help.
=sumproduct((year(daterng=year(a1))*(month(daterng =month(a1))*rngtosum)

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
I'd be grateful for your assistance please. I think I should know how
to
do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates,
April
2003, May 2003 and so on. The cells actually contain 01/04/03,
01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D)
and
a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice
Values
on
the Solutions sheet where the Invoice month and year match the month
and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I
can't
quite get my head round it.

I appreciate your help, thanks

Trevor

Don

thanks for your swift response. I had seen some of your replies to similar
questions in the Google archives so I was trying similar lines.

At the moment I have in cell C7:

=SUMPRODUCT(YEAR(LS_Inv_Dates)=YEAR(A7)*(MONTH(LS_ Inv_Dates)=MONTH(A7))*LS_I
nv_Values)

where LS_Inv_Dates is ='Solutions'!$D3:$D2000
and LS_Inv_Values is ='Solutions'!$F3:$F2000

Cell A7 on the Summary sheet has 01/04/03 displayed as April 2003.

In F3 on the Solutions sheet, I have 15/04/2003 and a value in F3.

Unfortunately, this results in a value of 0

Any further thoughts ? I'm not sure where I'm going wrong

Thanks

Trevor

"Don Guillett" wrote in message
...
this should help.
=sumproduct((year(daterng=year(a1))*(month(daterng =month(a1))*rngtosum)

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
I'd be grateful for your assistance please. I think I should know how
to
do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates, April
2003, May 2003 and so on. The cells actually contain 01/04/03,
01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D)
and
a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice Values
on
the Solutions sheet where the Invoice month and year match the month and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I
can't
quite get my head round it.

I appreciate your help, thanks

Trevor

Hi

Something like:
C2=SUMPRODUCT((Solutions!D2:D100=A2)*(Solutions!D 2:D100<=DATE(YEAR(A2),MONTH(A2)+1,0))*(Solutions!F 2:F100))


Arvi Laanemets




I'd be grateful for your assistance please. I think I should know how to do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates, April
2003, May 2003 and so on. The cells actually contain 01/04/03, 01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D) and a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice Values on
the Solutions sheet where the Invoice month and year match the month and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I can't
quite get my head round it.

I appreciate your help, thanks

Trevor

Don, Tom, Arvi

thank you very much for your incredibly quick and helpful responses. The
"faster formula" from Tom and the solution from Arvi both work. Tom's
solution *does* appear to be quicker as you can see the SUMPRODUCT
calculation "ripple down" as you drag the formula down the column.

Don ... worry not, no need to send the file now. Thanks.

Regards

Trevor


"Trevor Shuttleworth" wrote in message
...
I'd be grateful for your assistance please. I think I should know how to
do
this, but I don't. Hopefully you can save me a lot of time.

I have a column (A) on one sheet, "Summary", which contains dates, April
2003, May 2003 and so on. The cells actually contain 01/04/03, 01/05/03,
etc. (UK date formats)

On another sheet, "Solutions", there is a column of Invoice Dates (D) and
a
column of Invoice Values (F).

In column C on the Summary sheet, I want to subtotal the Invoice Values on
the Solutions sheet where the Invoice month and year match the month and
year in column A on the Summary sheet.

I think I should be using SumProduct or an array entered formula but I
can't
quite get my head round it.

I appreciate your help, thanks

Trevor