Wednesday, September 7, 2011

yogi_Sum Up Entries In A Range To Exclude Items As Specified

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
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!