Add up multiple columns if month+year equals

[Heinrix]

Hi,
I've spent quite a bit of time trying to figure out a way of doing this.
I'll give an example of my worksheet:-

Cost 1, Cost 2 , Date D/M/Y,
£10.00, £20.00, 15/12/2009,
£20.00, £10.00, 18/12/2009,
£10.00, £10.00, 24/12/2009,
£10.00, £10.00, 28/12/2009,
£10.00, £20.00, 05/01/2010,

The formula I currently have is
=SUMIF(C:C,"<="&DATE(2009,12,31),A:A)-SUMIF(C:C,"<"&DATE(2009,12,1),A:A)
But this will only add up column A and i want it to add column B as well for 1 month.

From what I've read it sound like i need to use SUMPRODUCT but i can't seem to get it with that either.

Sorry to trouble you and thanks in advance.

Martin

[sweep]

Hi,

Assuming your data starts at A1, and that the dates are correctly formatted,

=SUMPRODUCT((A2:B6)*(MONTH(C2:C6)=12)*(YEAR(C2:C6)=2009))

will give the result I believe you require.

[Heinrix]

Excellent! Thank You very much for the fast reply.
That should do the job except the data I want to to add up is in the entire B column and entire G column on my spreadsheet and its not very easy for me to move them around.

Could i use something like this:-

=SUMPRODUCT((B:B)*(G:G)*(MONTH(C:C)=12)*(YEAR(C:C)=2009))

or have I just messed that up?
I am using 2007 if that makes it any easier.

Thanks
Martin

[sweep]

You can use whole columns, but it will slow your sheet down significantly. You might want to do a quick search for dynamic ranges.

[Heinrix]

Thank you very much for the info.
For some reason i can't get =SUMPRODUCT((B:B)*(G:G)*(MONTH(C:C)=12)*(YEAR(C:C)=2009)) to work even if i set it like (b1:b100)*g1:g1000) it gets a value error. It works fine if its just for one row like B:B.
I will have a read into dynamic ranges like you suggested.
Thanks

[DonkeyOte]

You will get the #VALUE! error if any cell contains non-numerics (eg: headers in row 1) given the explicit coercion being undertaken by means of multiplication (and Month / Year functions)

[Heinrix]

AHHHH!! of course! how thick of me, thanks a lot.

[Heinrix]

Just tried that now and your right it has got rid of the #Value but now it just shows £0.00
The exact code I'm using is this =SUMPRODUCT(B2:B1000)*(M2:M1000)*(MONTH(G2:G1000)=12)*(YEAR(G2:G1000)=2009)
Am I still being a bit stupid or does that look OK?

[sweep]

=SUMPRODUCT((B2:B1000)*(M2:M1000)*(MONTH(G2:G1000)=12)*(YEAR(G2:G1000)=2009))

might work out a little better