Hi ffm,
SUMIFS can be tricky when dealing with dates. If you want to deal directly with comparing dates with 'months', you'll need to have Date and Time values entered in both cells being compared. It can be done, but Numbers is finicky about what is contained in the cells and what is displayed.
An easier method is to employ a 'helper' column to extract the month names from the dates in the data table, and present them as text versions of the month names, This allows you to enter the month names in column A of the summary table, compare those directly with the month names in the helper coumn, a task Numbers takes to more enthusiastically.
Here's an example, using the data in your sample table. The added column is the new column B, which may be hidden.
Table 2, the summary table, contains two versions of the formula shown below the tables. The version shown is entered in B2, then filled down to the end of column B.
The second version, shown below, is entered in C2 and filled down to the end of column C.
C2: IF(COUNTIF(Table 1::B,A2)<1,"",SUMIF(Table 1::B,A2,Table 1::C))
The part shown in bold is the core formula that does the work—the same formula as used in column B.
The part in 'normal' type is an added 'switch' that prevents calculation until there is at least one copy of the month name for that row in column B of Table 1.
Note: because the left-right order of the Inc and Exp columns is not the same on both tables, this formula cannot be entered in column B, then filled right to work in column C.
Table 1:
Note that I have converted rows 1 and 2 to Header rows, and the bottom two rows to Footer rows. Doing this allows specifying all of the non-header, non-footer cels of a column using only the column letter as the address, as in the Tale 1::B and Table 1::C references above.
This table contains one new formula, entered in B3 and filled down to the last regular cell in column B (in the example, B12).
B3: IF(LEN(A3)<1,"",MONTHNAME(MONTH(A3)))
Like the formula above, this one uses IF as a 'switch' to prevent calculation (and the error that would result) until there is an entry made in 'this row' of column A. If a date has been entered, the bold part of the formula extracts the month value (a number) from the date, then translates that number to the matching month name.
This column should be hidden to prevent accidental damage to the formula.
As you need more row for data entries, you can add then by grabbing the row control handle at the bottom of the row reference tab list and dragging down. The new rows will be added above the footer rows.
Footer row formulas:
These are probably similar to what you already have, but the SUM formulas do have the advantage of being in footer rows.
Table 1::C13: SUM(C)
Table 1::D13: SUM(D)
Table 1::C14: C$1+D13−C13
Regards,
Barry
