Sum Product based on Date

[Christopherdj]

Hi

See the attached file.
My query is based on a range of data contained on a 'Yearly" sheet.
Using the Monthly sheet in row 12, and using the cell ranges already populated, I would like to add the data in the ranges based on up to and including the date specified in G11 on the monthly sheet.
Obviously as the date is changed, the totals will change.
I've tried this formula but get a #VALUE error.

Please Login or Register  to view this content.

Can anyone help please.

[marreco]

Try
=SOMARPRODUTO((Yearly!$E$3:$E$1048576=Monthly!G11)*Yearly!$F$3:$F$1048576)

[Christopherdj]

Thanks

Please Login or Register  to view this content.

Seems this one works ok - Look no further down post, errors somewhere.
This one is fine for suitable outcome when changing dates.

[daddylonglegs]

The SUMPRODUCT version will treat all empty cells in column E as zero and therefore < G11, so you get rows 3 and 4 included in the count, for example, whatever the date in G11, is that what you need? If not then perhaps try SUMIF using the total column BR, i.e.

=SUMIF(Yearly!$E:$E,"<="&$G$11,Yearly!$BR:$BR)

That will ignore rows without an entry in column E

[Christopherdj]

Thanks Daddylonglegs, That doesn't work as I'm adding sections of the totals.
IN Monthly sheet, I'm only adding portions in A13, B13, C13, D13 and E 13 with the total combined in F13.

I modifed your formula to this:

Please Login or Register  to view this content.

And it doesn't add all cells in the ranges specified.
Does it have something to do with the empty cells with no dates in Column E of the yearly sheet?