Return results from the last 12 months

[Gareth1000]

I have 65,000+ lines of data in a sheet where column A is the date, column E is the name, and column J is the monetary amount received. In a seperate sheet (same workbook) I have a report using formulae referencing the data in the data sheet. On the report sheet I have 10 columns (D to M) for each client or client group (e.g. a family) as there are different names I want it to search for in column E in the data sheet.

To return income received for a given year I have a formula that works - =SUMPRODUCT(SUMIFS(data!$J:$J,data!$E:$E,$D3:$M3,data!$A:$A,">=1/1/"&V$2,data!$A:$A,"<=31/12/"&V$2)) - where 'V2' is the year at the top of the column.

I now want a formulae that will return:

The income received in the last 12 months from today, or preferably
The income received in the last 12 months from the end of last month

I have tried this - =SUMPRODUCT(SUMIFS(data!$J:$J,data!$E:$E,$D3:$M3,data!$A:$A,">=TODAY()-365",data!$A:$A,"<=TODAY()")) - but it returns '0.00' when I know there is income.

Any ideas? TIA!

[Phuocam]

Try this ...

=SUMPRODUCT(SUMIFS(data!$J:$J,data!$E:$E,$D3:$M3,data!$A:$A,">="&TODAY()-365,data!$A:$A,"<=TODAY()"))

[Gareth1000]

Not quite, but I fiddled around with it, accepted a correction from MS, and got this that works!

=SUMPRODUCT(SUMIFS(data!$J:$J,data!$E:$E,$D3:$M3,data!$A:$A,">="&TODAY()-"365"))

THANKS!

[Gareth1000]

Not quite, but I fiddled around with it, accepted a correction from MS, and got this that works!

=SUMPRODUCT(SUMIFS(data!$J:$J,data!$E:$E,$D3:$M3,data!$A:$A,">="&TODAY()-"365"))

THANKS!