0
I have a sheet of transactions categorized as either income or expenses. I would like to populate expenses which are more than a certain value, say, more than $5,000, as a list in another sheet. I tried various combinations of IF, SUMIFS, VLOOKUP, INDEX, MATCH but just cant get it right. Any ideas???
Rakesh
Posted 2017-07-01T12:03:30.433
Reputation: 1
1
Have you tried a helper column?
Let's say you have the following setup: Column A: Type (Income or Expense) Column B: Amount
Put this formula in Column C:
=IF(AND(A1="Expense",B1>5000),B1,0)
This will populate Column C with either:
1. 0 if the amount is an Income Type or is an Expense Type less than $5,000.
2. The actual value of the Expense Type if the Expense Type is greater than $5,000.
You can then perform your lookup on Column C, which will return either 0 or the actual expense amount, if it is greater than $5,000.
Bruce Kaufmann
Posted 2017-07-01T12:03:30.433
Reputation: 36
1The question is about writing the result in another sheet – yass – 2017-07-02T20:44:33.327
Understood. You can have the lookup formula - VLOOKUP, INDEX/MATCH, whatever - in the second sheet to pull values from the 1st sheet. I am assuming that in the second sheet, the user will also want a description of the expense, not just the amount. After all values are pulled into the second sheet, the user can manage the data in the column of numbers to eliminate zero values. I'm not certain, but if the user wants to only populate the list with the non-zero values, some VBA may be required to store the non-zero values, then print them. – Bruce Kaufmann – 2017-07-03T02:32:01.247