Home

Spreadsheet Search

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

@Eggman 

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. 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies