Forum Discussion
#excel formula - which formula to use to match text that link to the amount and output total in mth
Patrick2788 @mathetes,
Appreciated your help and reply
as I have not control of the description input send over, I not sure if excel is smart enough to pickup up exact text eg in the summary - USS event or UUS that are related to it, calculate the amount relate to it within that month into the spreadsheet.. ha
or I need to clean up the text as per what mathetes has mentioned.
Here's my sample workbook, please feel free to edit and let me know how it can work.
The data tab are not suppose to be sort. 🙂
Regards
You wrote: as I have not control of the description input send over
But I'm hoping you DO have enough control to get whoever it is at the other end of your input data to start being more disciplined about how they enter the data. In the attached, I've added a column that uses data validation to restrict the entries in it to match the acceptable texts for your summary report's budget categories. They can use the adjacent column to freestyle whatever supplemental info they might wish to enter.
It appears you are able to write formulas that will total the amounts per month per category. So if you can get the data entry folks to use a spreadsheet that has data integrity, then you should be home.
You might also find @Patrick2788's suggestion of the Pivot Table to work even more easily than writing all those formulas to summarize these categories. I have my own personal budget tracking spreadsheet set up to take thousands (over the course of a year) of entries, from multiple credit card and bank accounts, and summarize the data--after I standardize each expense or income into a set of consistent budget categories--via a Pivot Table. No formulas are required; it updates easily.... You just have to have the raw data created following some sort of disciplined, consistent way. And data validation, using a table taken from the acceptable categories, will give you that.