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

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

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

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

I believe a faster formula would be

=SUMIF(solutions!$D$1:$D$500,"="&A1,solutions!$F$ 1:$F$500)-SUMIF(solutions!
$D$1:$D$500,""&DATE(YEAR(A1),MONTH(A1)+1,0),solut ions!F$1:$F$500)

Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...

=SUMPRODUCT((MONTH(solutions!$D$1:$D$500)=MONTH(A1 ))*(YEAR(solutions!$D$1:$D
$500)=YEAR(A1))*solutions!$F$1:$F$500)


Regards,
Tom Ogilvy

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

I put another bracket in after SUMPRODUCT and Excel forces another at the
end. It would seem this isn't needed but doesn't do any harm.

I know this should be more straightforward than it is working out but I
can't see what is wrong.

Could I send you the workbook to check first hand? It's something I'm
putting together for my wife so it's only got test data. There is a short
macro to generate an Invoice Number but other than that there's no code.

Thanks

Trevor

"Don Guillett" wrote in message
...
It appears that you also forgot a ( right after SUMPRODUCT



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


=SUMPRODUCT((YEAR(LS_Inv_Dates)=YEAR(A7)*(MONTH(LS _Inv_Dates)=MONTH(A7))*LS_
I
nv_Values)


--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
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

Maybe send to someone with your dating format.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
Don

I put another bracket in after SUMPRODUCT and Excel forces another at the
end. It would seem this isn't needed but doesn't do any harm.

I know this should be more straightforward than it is working out but I
can't see what is wrong.

Could I send you the workbook to check first hand? It's something I'm
putting together for my wife so it's only got test data. There is a short
macro to generate an Invoice Number but other than that there's no code.

Thanks

Trevor

"Don Guillett" wrote in message
...
It appears that you also forgot a ( right after SUMPRODUCT




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



=SUMPRODUCT((YEAR(LS_Inv_Dates)=YEAR(A7)*(MONTH(LS _Inv_Dates)=MONTH(A7))*LS_
I
nv_Values)


--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
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

Dating format would have no effect in this instance.


--
Regards,
Tom Ogilvy

Don Guillett wrote in message
...
Maybe send to someone with your dating format.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
Don

I put another bracket in after SUMPRODUCT and Excel forces another at
the
end. It would seem this isn't needed but doesn't do any harm.

I know this should be more straightforward than it is working out but I
can't see what is wrong.

Could I send you the workbook to check first hand? It's something I'm
putting together for my wife so it's only got test data. There is a
short
macro to generate an Invoice Number but other than that there's no code.

Thanks

Trevor

"Don Guillett" wrote in message
...
It appears that you also forgot a ( right after SUMPRODUCT





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




=SUMPRODUCT((YEAR(LS_Inv_Dates)=YEAR(A7)*(MONTH(LS _Inv_Dates)=MONTH(A7))*LS_
I
nv_Values)


--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
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

Didn't know since I have no experience with "backwards" date.
SEND

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Tom Ogilvy" wrote in message
...
Dating format would have no effect in this instance.


--
Regards,
Tom Ogilvy

Don Guillett wrote in message
...
Maybe send to someone with your dating format.

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
Don

I put another bracket in after SUMPRODUCT and Excel forces another at
the
end. It would seem this isn't needed but doesn't do any harm.

I know this should be more straightforward than it is working out but
I
can't see what is wrong.

Could I send you the workbook to check first hand? It's something I'm
putting together for my wife so it's only got test data. There is a
short
macro to generate an Invoice Number but other than that there's no
code.

Thanks

Trevor

"Don Guillett" wrote in message
...
It appears that you also forgot a ( right after SUMPRODUCT






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





=SUMPRODUCT((YEAR(LS_Inv_Dates)=YEAR(A7)*(MONTH(LS _Inv_Dates)=MONTH(A7))*LS_
I
nv_Values)


--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Trevor Shuttleworth" wrote in message
...
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

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