SUMIFS formula

gmagerr

New Member
Joined
Jul 18, 2010
Messages
41
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Unclear... If you have just one entry in O that's 2011 with the corresponding N-cell being 2010 and A-cell Yes, you'll get just the corresponding L-cell value as the formula result. And, this is the correct behavior.
 

gmagerr

New Member
Joined
Jul 18, 2010
Messages
41
Thanks for the reply Aladin, I actually got it to work like this.
=SUMIFS(L:L,A:A,"Yes",N:N,2010)+SUMIFS(L:L,A:A,"Yes",O:O,2011)
I still have to test it thoroughly
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,998
Messages
5,508,682
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top