0

I'm trying to build out a budget monitoring spreadsheet. I want to pull sum of entries by category and based on the month. The data is in rows as follows and will grow each month as I download our spend. Here is the sample data:

Date       Category    Amount
03-15-21   Auto        500.00
02-12-21   House       375.00

Ultimately I'll have it displayed with months across columns and categories in rows so we can monitor our budget.

How can I pull out the data by category and month?

I've looked at sumifs, sumproduct, index match, haven't found a solution. I appreciate any guidance.

3 Answers 3

1

Pivot tables! Select your whole table, click insert > pivot table. Follow the prompts until you get the Pivot Table Fields side bar. You must now drag fields into the correct boxes below. You can play around to get it just how you like it. But for a start,

Leave Filters empty. Drag Date into "Columns" section. Drag Category into "Rows" section. Drag Amount into "Values" section.

If Excel recognises your dates correctly, the pivot should auto group per month. If not, you could also insert a new column B with header Month, and add the formula =EOMONTH(A2,0) to B2. Use Month instead of Date in the Pivot table. You must refresh your pivot table if you add the column after creating the Pivot in the first place. If EOMONTH still gives an error, make sure your date format match your regional date format setting.

Tip: Pivot tables and charts is an absolute gem of a feature in Excel. It can be confusing to grasp at first, but once you get it you will never look back. Perhaps watch a quick "how to use pivot tables" video on youtube.

1

You can use PivotTable directly:

enter image description here

0

This solves the issue:

enter image description here

How it works:

  • Formula in Y34 for Helper Data:

    =TEXT(V34,"mmmm")

  • Array (CSE) Formula in Z34 for Unique Month Name:

    {=IFERROR(INDEX($Y$34:$Y$49,MATCH(0,COUNTIF($Z$33:Z33,$Y$34:$Y$49),0)),"")}
    
  • Array (CSE) Formula in AA33 for Unique Category Name:

    {=IFERROR(INDEX($W$34:$W$49,MATCH(0,COUNTIF($Z$33:Z33,$W$34:$W$49),0)),"")}
    

N.B.

  • Finish array formula with Ctrl+Shift+Enter.

  • For neatness later on you may hide Helper data in column Y.

  • Formula in Cell AA34:

    =SUMPRODUCT((TEXT($V$34:$V$49,"MMMM")=$Z34)*($W$34:$W$49=AA$33)*($X$34:$X$49))
    

Fill formula across the range.

Adjust cell references in the formula as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .