Numbers formula for rolling 12 month sum

Question

I have a table with income data over a 2 year period showing columns: date, description, job1, job2, job3, (the income shown is for 3 different work jobs over a period of time).

In a separate table I would like to calculate my income over a rolling 12 month period but can't work our how to do it.

Could someone help me please?

here are the simplified tables, so with this basic data I can do the basic sum (as sown). but in the complete table 1 there are hundreds of entries so I need a formula to calculate the figures in table 2 please. needs to be a rolling 12 months figure for each heading. thank you

Answer 1

You can do this using the SUMIFS() function in Numbers. The SUMIFS() function allows us to conditionally add up a group of numbers with multiple criteria.

The picture below shows how to do it using this function based on the samples of the input data table and the outcome table you have given. Pls pay attention to the absolute references in the formula, i.e. the "$" signs; they are necessary for the formula to work correctly when you copy & paste it to other cells.

If you have any difficulty in creating the formula in cell E5 based on the picture below alone, you can recreate it by typing its following form (or copy & pasting it from this post) into an appropriate cell.

= SUMIFS(Table 1::E$2:E$7,Table 1::$A$2:$A$7,">="&$A5,Table 1::$A$2:$A$7,"<="&$B5)

Note, however, if your actual input data table is placed differently (the one in the example I gave, including the header row, starts from A1) or bigger than the one in the example I gave, you would need to change some or all of the cell references in the example formula I give here.