Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
Nomad65 said:
I want to add up the contents of a column conditional on the value in another column
I'm creating a spreadsheet of monthly expenses, and I want to be able to selectively include/exclude certain entries based on the contents of another cell in the same row. For example, let's say column E contains the expense amounts, and column F contains a code such as "house" or "car".
I have a total for "all monthly expenses", but now I want to create totals for things like "all monthly expenses if I no longer had a car" -- and for that one, I'd exclude all the column E entries with the note "car" in column F.
I could do it brute force with a nasty set of "IF" statements. And of course I could copy the entire table and add a formula that changes the values to zero depending on the contents of column "F". But I'd like to do it more elegantly, and in a way that easily expands if I add new expenses to the spreadsheet. I have the sense that some combination of VLOOKUP and IF could do it, but I can't figure it out.
Thanks for any help!
--------------------------------------------------------------
Nomad65 said:
I want to add up the contents of a column conditional on the value in another column
I'm creating a spreadsheet of monthly expenses, and I want to be able to selectively include/exclude certain entries based on the contents of another cell in the same row. For example, let's say column E contains the expense amounts, and column F contains a code such as "house" or "car".
I have a total for "all monthly expenses", but now I want to create totals for things like "all monthly expenses if I no longer had a car" -- and for that one, I'd exclude all the column E entries with the note "car" in column F.
I could do it brute force with a nasty set of "IF" statements. And of course I could copy the entire table and add a formula that changes the values to zero depending on the contents of column "F". But I'd like to do it more elegantly, and in a way that easily expands if I add new expenses to the spreadsheet. I have the sense that some combination of VLOOKUP and IF could do it, but I can't figure it out.
Thanks for any help!
--------------------------------------------------------------
This is great. I'm stuck how ever trying to get a sum of values with the criteria being a date, can you help with that? For example I have a bunch of expenses and dates that they were made and I want to get all of the expenses that were made on a particular date.
ReplyDeleteThanks!
Better yet, how do I make the criteria value based on a cell value rather than explicitly stating a value?
ReplyDelete