Hi all,
I'm trying to calculate a column if multiple criteria are met. I discovered the SUMIFS function which seemed like it would do what I needed, however I'm having some trouble. I'm creating a spreadsheet to calculate power consumption in our computer center. The columns are all part of a table.
The columns I need to calculate are titled
Include Column A - (Yes,No)
Baseline Column N - (2006 through 2020) year I'm starting this. Value now is 2010
Added Column O - (2006 through 2020) Year new equipment is added. Value now is 2011
Removed Column P (Not used in this formula yet) - (2006 through 2020) Value will be what I take out that year
kVA Column L - this is the main column I want calculated
These are all being populated via data validation from another spreadsheet called Input Data (don't know if that matters)
Here's my formula.
=SUMIFS(L:L,A:A,"Yes",N:N,2010,O:O,2011)
The issue is
If I remove Column O from the formula, it works.
If I add a record, and put 2011 in column O (nothing else in column O but this one entry) and there is no 2010 in the column N cell next to it, I get zero in the calculation cell.
If I put 2010 in Column N next to the 2011 in Column O, I'll get only what's in column O for 2011 calculated.
It appears, that all of the cells in columns N and O need to be populated for this to work, but then like I said it only calculates what's in column O at that point.
BTW, in Column A, if the entry is "No" the row doesn't get calculated, if the entry is "Yes" the row gets calculated, so that part is working. Columns N and O seem to be where the problem is.
Any Ideas?
Thanks
I'm trying to calculate a column if multiple criteria are met. I discovered the SUMIFS function which seemed like it would do what I needed, however I'm having some trouble. I'm creating a spreadsheet to calculate power consumption in our computer center. The columns are all part of a table.
The columns I need to calculate are titled
Include Column A - (Yes,No)
Baseline Column N - (2006 through 2020) year I'm starting this. Value now is 2010
Added Column O - (2006 through 2020) Year new equipment is added. Value now is 2011
Removed Column P (Not used in this formula yet) - (2006 through 2020) Value will be what I take out that year
kVA Column L - this is the main column I want calculated
These are all being populated via data validation from another spreadsheet called Input Data (don't know if that matters)
Here's my formula.
=SUMIFS(L:L,A:A,"Yes",N:N,2010,O:O,2011)
The issue is
If I remove Column O from the formula, it works.
If I add a record, and put 2011 in column O (nothing else in column O but this one entry) and there is no 2010 in the column N cell next to it, I get zero in the calculation cell.
If I put 2010 in Column N next to the 2011 in Column O, I'll get only what's in column O for 2011 calculated.
It appears, that all of the cells in columns N and O need to be populated for this to work, but then like I said it only calculates what's in column O at that point.
BTW, in Column A, if the entry is "No" the row doesn't get calculated, if the entry is "Yes" the row gets calculated, so that part is working. Columns N and O seem to be where the problem is.
Any Ideas?
Thanks
