Tracking Expenses While Sorting

[TSCworkhelp]

Hello. I'm trying to make a spreadsheet that tracks our expenses by category and quarter. Right now I update the costs manually with the sum formula, after sorting by category and date, but if I ever need to sort the sheet differently all of the sums get mixed up. Is there a way to track the costs by category and date regardless of how it gets sorted afterwards?

To clarify, if we make a supplies purchase in Q1 for $50 and another in Q2 for $100, I need the total in my grid at the bottom to show "Supplies: $150" for the year but still list the costs per quarter separately.

I've attached a sample workbook (I hope I did it correctly). If it helps, our quarters are Q1: Oct-Dec, Q2: Jan-Mar, Q3: Apr-Jun, Q4: Jul-Sep.

Thank you!

[hall885]

Hi - have you tried using a SUMIF formula to keep track of spending for each quarter? You may need to add a column based on the date to define which dates belong to which quarter, but that should keep the quarters and the overall total correct regardless of order.

[sandy666]

maybe this one with PivotTable
[EDIT:] or with custom defined quarters (PowerQuery and PivotTable)

[FlameRetired]

Edited Had whole column references. Did not notice table below data.

our quarters are Q1: Oct-Dec, Q2: Jan-Mar, Q3: Apr-Jun, Q4: Jul-Sep.

With that in mind find this formula in helper column H.

Please Login or Register  to view this content.

Then this summary table in J:0 and this formula K2:O5

Please Login or Register  to view this content.

	J	K	L	M	N	O
1	Qrts	Additional Training	Local Travel Expenses	Advisory Board	Printing	Office Supplies
2	1	145	192.96	304.95	140.99	260.47
3	2	0	0	0	0	611
4	3	0	0	0	0	0
5	4	0	0	0	0	0
6	Year Totals →	145	192.96	304.95	140.99	871.47

[marketshare]

I think SUMPRODUCT may help you with this. Please see the attached file.

[TSCworkhelp]

Wow, these are fantastic! Thank you!! I need to significantly improve my Excel skills overall, so I'll be playing with all of it to figure out how it was done and which would be best to move forward with.

@FlameRetired: What are the 8 numbers referring to at the end of this formula? =LOOKUP(MONTH(F18),{1,4,7,10},{2,3,4,1})

Thanks again, I really appreciate it

[FlameRetired]

What are the 8 numbers referring to at the end of this formula? =LOOKUP(MONTH(F18),{1,4,7,10},{2,3,4,1})

They are the lookup_vector and [result_vector].

LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP finds the nearest match less than or equal to the number that MONTH returns. It then finds the value in the [result_vector] (quarter) corresponding to that match.

Since the 1st quarter begins in Oct (10th month) one is assigned the last position in the [result_vector] (the quarters). The others correspond accordingly.

Did that help?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[TSCworkhelp]

Yes, that makes sense and I understand how it was written now.

In order to upload a sample workbook I had to edit/delete a lot of financial information, but unfortunately I deleted full columns in the process, so I need to try recreating what you did. It will certainly be good practice for me. Thank you all very much for your help!