SumProduct or Array Function for summing by month and year

[Trevor Shuttleworth]

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 Guillett]

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

 

[Tom Ogilvy]

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

Regards,
Tom Ogilvy

 

[Arvi Laanemets]

Hi

Something like:
C2=SUMPRODUCT((Solutions!D2100>=A2)*(Solutions!D2100<=DATE(YEAR(A2),MONTH(A2)+1,0))*(Solutions!F2:F100))


Arvi Laanemets

 

[Trevor Shuttleworth]

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]

We all forget a ) once in awhile.

 

[Tom Ogilvy]

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),solutions!F$1:$F$500)

Regards,
Tom Ogilvy

 

[Trevor Shuttleworth]

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]

Maybe send to someone with your dating format.

 

[Tom Ogilvy]

Dating format would have no effect in this instance.

 

[Don Guillett]

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

 

[Trevor Shuttleworth]

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