Mar 28 2019 11:52 AM
I'm a tax accountant and receive spreadsheets with expenses listed by categories such as "travel, utilities,..." in one column then the amount of the expenditure in another column over a row by date (often from bank statments). Sometimes there are hundreds of rows. Is there a way to search by an expense field such as utilities that will give me all of the utility amounts either listed or totaled??
Mar 28 2019 12:04 PM
I'm moving your question to the Excel space, which is the appropriate place to post Excel questions in the future. Thanks!
Mar 28 2019 09:34 PM
Mar 29 2019 04:06 AM
To sum or count a list by category is straightforward using 'IFS' formulae
= SUMIFS( Amount, Category, "Utilities" )
= COUNTIFS( Category, "Utilities" )
Although it is moderately straightforward to produce a filtered list using an index column and SMALL to pick out the rows that match the criterion, for the moment it might be best to stick with a simple filter and manual interaction.
If you are an Office 365 user then over the next few months you should see additional functionality including a FILTER function.
= FILTER( Table1, Category="Utilities" )
Note: I have assumed an Excel Table. The data can either be loaded into a table or the table structure can be applied to an existing dataset using Ctrl+T.