Extract data to summary

I have another simple spreadsheet to track expenses. It consists of 13 sheets: Summary and Jan-Dec. I laboriously clicked all over to populate the summary sheet, which was time consuming. Then I checked here to see if there was an easier way to do this. I noted some threads stating keep all the expense entries in one table, so that is what I did.

But... wanting a monthly/YTD summary is trying! I have a number of categories. I can get the category for the YTD in the Summary, but I cant seem to be able to get it per month.

How can I add to the Summary categories on a month-month basis? I do have it the other way, but this new way looks easier if I can separate the months in the Summary table.

Pictured below is the summary & expense tables...

TIA!

iMac, macOS High Sierra (10.13), 27-inch Late 2009

Posted on Aug 16, 2018 10:26 AM

Top-ranking reply

Yellowbox

Level 7

21,939 points

Posted on Aug 18, 2018 10:04 AM

Hi Pete,

I have simplified the tables to leave just one Header Row (Row 1).

Here we go with data for January and February.

Formula in C2 of the Categories table (and Fill Right and Fill Down) =SUMIFS(2018 Expenses::$E,2018 Expenses::$C,$A2,2018 Expenses::$F,C$1)

Enter the months in Column A of the Categories table as text to prevent Numbers automatically formatting as Date & Time.

Type an apostrophe as in 'January to force Text format.

Formula in Header Column B of Categories =SUM(2:2)

Formula in Footer Cell B2 of Categories =SUM(B) and Fill Right.

Formula in C2 of 2018 Expenses =MONTHNAME(MONTH($B2)) and Fill Down.

Please call back with questions.

Regards,

Ian.

View in context

Extract data to summary

Similar questions