Forum Discussion
IF OR statements
I have a cashbook spreadsheet which in Col A shows money in, Col B shows money out, Col C shows the cost code, and Cols D, E, F, etc one for each of the cost codes. So, for example, if there's a cost of £100 in Col B and a "Gardening" cost code in col C then the value in Col B is copied over to, let's say, Col F which is the Gardening costs column. That's easy. In the Gardening costs column is F and there's a gardening cost in row 10, we have: =IF(C10="Gardening",F10=B10,"")
But suppose there's money coming in, for example, a refund for gardening overcharged. This income would be shown in Col A. What I want to do is to repeat the above but add something along the lines that if there's an income in Col a, that should also be moved to the gardening column but as a negative amount.
I've struggled to write this without success. any help greatly received!
Thanks to the community.
G.
4 Replies
Many thanks HansVogelaar. I thought your solution to be a very good one but SnowMan55's is even simpler, one which I've already implemented. Many thanks for your help. G
Personally, I would recommend an altogether different approach, as illustrated on the attached file. Keep a simple record of each transaction--what you have in your first several columns already, making sure you include a date, budget category, code for income or expense (Cash flowing In or Out), and amount. You have a choice: record income as positive, expenses as negative or use a formula based on the code to turn the amount to positive or negative in a final column.
Then let the Pivot Table do all the summarizing for you, by month and budget category. Summary is down without any formulas.
Many thanks for your suggestion. In fact, SnowMan55 came up with a very neat simple solution not involving IF/OR nor Pivot tables. Perfect for what I need to achieve. Thanks again for your help.
This will be easiest if the headers in D1, E1, F1 etc. correspond exactly to the cost codes entered or selected in column C.
In D2:
=IF($A2=D$1, -$A2, IF($B2=D$1, $B2, ""))
Fill to the right then down (or vice versa)
