Spreadsheet Search

Occasional Visitor

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??

3 Replies

I'm moving your question to the Excel space, which is the appropriate place to post Excel questions in the future. Thanks! 

In which columns are your expenses, listed amounts, and total amounts?


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.