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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,272
Messages
5,657,775
Members
418,413
Latest member
Radoslaw Poprawski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top