Sunday, January 15, 2017

Working On Filtered Data

Problem no: 1
Let's assume there is a table in Excel with 10 rows and having data in columns A to J.

How do we find sum of all  the cells in all the rows and columns mentioned above. There are filter conditions to be applied on some rows as well as on some columns. 

Assume we have frozen the panes in the excel so that row headings are frozen to the left (say cols A and B) and column headings (say row 1) on top. Now filter conditions have to be applied on cols A and B and row 1. 

Countifs and Sumifs won't work on both rows and columns. How will you address this?

Problem no: 2
Now let's assume we need to find the minimum (or max or average) value in a column while also applying a filter on another (or same) column. How do we do that?

Additional reading:
A friend sent me this nice article on Vlookup: https://exceljet.net/things-you-should-know-about-vlookup

No comments:

Post a Comment

Popular Posts

Featured Post

Whom Do We Trust

I came across this: APNews being the trusted source of news for half the world.  And there is Truth Social which also is read and trusted by...