Seriously clever formula required...!

Hi James

Essentially, you need to collate across two categories - month, and expense type.

---

Create another column (which will be hidden) in the Expenses sheet. Call it 'collated' (for example).

Populate that column with this formula:

=MONTH($Date) & $Category

This will produce entries like 7T1 (expenses in July for category T1)

---

In the Profit and Loss sheet, create a header row called MonthID (hidden), which has the number for each month in corresponding columns.

In each Expenses cell, put this forumla:

=SUMIF(Expenses::$collated, "=" & $monthID & $Category, Expenses::$Nett)

This should put the total for that category for that month into each cell.

---

You can create extra header columns and rows, which are used for formulae, but hidden from view.

I might need to refine this, but that's a start 🙂

Hi James

I was working off the visible headings in your sheets.

Numbers only references actual Numbers header rows and columns.

Create a header row at the top of your sheet, which will use the same headings you have in the visible section.

Hide the header row.

The MonthID is a header column, at the side, so the name will be in the leftmost column.

Here's a screenshot with a simplified version of your spreadsheets.

The green parts are Numbers Headers. These can be hidden, and will still work.

I have highlighted the relevant columns and rows that are involved with the formulae.

Note that it is important to use the same upper and lower case when using header references that you have created.

Also, there is no need for a MonthID column in the Expenses sheet, as using MONTH($Date) will produce the required result.

Hi James,

You wrote:

"CELL D20 = £197.50 (which is the sum of all category 'T1' transactions in period 'April 6th - 30th')

CELL D22 = £149.19 (which is the sum of all category 'T3' transactions in period 'April 6th - 30th')

CELL E20 = £197.50 (which the sum of all category 'T1' transactions in period 'May')

"

Essentially, what you are looking for is a SUMIFS statement.

Here are two possibilities, both shown in the example:

The small table on the left is a representation of part of your Expenses table, showing the expenses in your example.

The two tables on the right are representations of the active part of your P&L table, showing category totals for the sample expenses in the Expenses table. The error warning triangles in the lower table are expected, and are explained below. Several rows are hidden to reduce the size of the screen shot. The tables differ only in the formula used.

(Upper example):

D20: =SUMIFS(Expenses :: $E,Expenses :: $A,$A20,Expenses :: $D,">="&D$1,Expenses :: D,"<"&EOMONTH(D$1,0)+1)

(Lower example):

D20: =SUMIFS(Expenses :: $E,Expenses :: $A,$A20,Expenses :: $D,">="&D$1,Expenses :: D,"<"&E$1)

In each case, the fornula is filled down from D20 to the end of the categories, and filled right to the last column.

As the second formula references the header cell containing the full date at the top of its column AND the cell containing the date in the next column to the right, the lower version of the table will require one column beyond the last one for which sums are being reported

Both formulas reference the Date and Time value in the cell in row 1 of their own column. This MUST be a true Date and Time value, MUST be the first day of the period to be summed in its column, and MAY need to match the format of the Date and Time value used in column D of the Expenses table. (Note that I used April 1 for the first date. This can be changed to April 7 if you want to eliminate any entries prior to that date.)

The category codes in column A must match exactly those used in column A of the Expenses table.

You will need to change "Expenses" in the formula to the actual name of your Expenses table.

Row 1 is a 'working row', necessary to the formulas, but optional to the user's view. It may be hidden.

Row 2 of the P&L table uses a formula to extract the month from the date in row 1, and display the name of that month.

D2: =MONTHNAME(MONTH(D1))

Fill right a needed.

Details on the functions used may be found in the iWork Formulas and Functions User Guide. The guide may be downloaded via the Help menu in Numbers.

Regrds,

Barry

This does lead to a shorter formula.

If I'm reading your screen shots correctly, this should work. Two versions of the formula again, plus a variation that will be required if you have more than one table named (eg.) "May".

Data tables:

P&L table (and Sheets list):

D20: =SUMIF(April :: $A,$A20,April :: $E)

This is the 'simple, straightforward version. It can be filled down the column from D20, and will automatically adjust the $A20 reference to fit the new row. It will NOT adjust to the new tabe name as it is filled right.

E20: =SUMIF(INDIRECT(E$1&"::$A",),$A20,(INDIRECT(E$1&"::$E",))

This is the same formula, but uses INDIRECT to construct the first argument of SUMIF from the table name (eg. May) in row 1 of its column plus the text "::$A". This formula may be filled in both directions.

Variation:

The above formulas will work if each of the table names is unique within the document. If you have more than one table named with the month, the references to columns A and E must include the name of the sheet containing the target table.

D20: =SUMIF(Expense log::April :: $A,$A20,Expense log::April :: $E)

E20: =SUMIF(INDIRECT("Expense log::"&E$1&"::$A",),$A20,(INDIRECT(("Expense log::"E$1&"::$E",))

Regards,

Barry

Hi James,

Further explanation 🙂

Collating requires adding just one extra column to the original Expenses table (and making sure the top row is a Header):

=MONTH($Date) & $Category

since you are collating for both month and category

---

The original Profit and Loss table required a single Row Header with the label Category in the appropriate column; also a single Column Header, with one extra row named MonthID.

There's no need for a formula for MonthID, as the values are stable - you will likely have the same layout year after year. Just enter the first couple of numbers, and drag to fill the series.

The formula for the totals is brief, and can be replicated to all cells, without any changes.

=SUMIF(Expenses::$Collated,"="& $MonthID & $Category, Expenses::$Nett)

=SUMIF(..., Expenses::$Nett)

Sum all Nett expenses, if ...

Expenses::$Collated,"=" & $MonthID & $Category

If the values in the Collated column equal the MonthID and the Category for this cell.

To me, this seems like the least work, for the required result.

Still, like you, I often explore several options before deciding on the solution that suits me best 🙂

HI James,

Always good to hear a comment like that.

Thanks for posting it.

Regards,

Barry