SUMIF behaves differently for different columns in table.

[overlordspock]

I have a workbook with two tabs. Each tab has a table.

Tab 1 - FY16
Table Name: Previous Year
[TABLE="width: 250"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Phase 1[/TD]
[TD]Phase 2[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD="align: right"]3,616,055[/TD]
[TD="align: right"]790,228[/TD]
[TD="align: right"]4,406,283[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD="align: right"]4,100,174[/TD]
[TD="align: right"]790,095[/TD]
[TD="align: right"]4,890,269[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD="align: right"]4,181,286[/TD]
[TD="align: right"]936,072[/TD]
[TD="align: right"]5,117,358[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD="align: right"]4,179,263[/TD]
[TD="align: right"]1,098,208[/TD]
[TD="align: right"]5,277,471[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD="align: right"]4,666,161[/TD]
[TD="align: right"]1,118,461[/TD]
[TD="align: right"]5,784,622[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD="align: right"]3,120,829[/TD]
[TD="align: right"]754,091[/TD]
[TD="align: right"]3,874,920[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD="align: right"]3,245,530[/TD]
[TD="align: right"]499,353[/TD]
[TD="align: right"]3,744,883[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD="align: right"]2,715,427[/TD]
[TD="align: right"]706,040[/TD]
[TD="align: right"]3,421,467[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD="align: right"]2,696,728[/TD]
[TD="align: right"]660,692[/TD]
[TD="align: right"]3,357,420[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD="align: right"]3,147,961[/TD]
[TD="align: right"]793,246[/TD]
[TD="align: right"]3,941,207[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]3,173,236[/TD]
[TD="align: right"]815,035[/TD]
[TD="align: right"]3,988,271[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]2,763,259[/TD]
[TD="align: right"]771,438[/TD]
[TD="align: right"]3,534,697[/TD]
[/TR]
</tbody>[/TABLE]

Tab 2 - FY17
Table Name: Current Year
[TABLE="width: 250"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Phase 1[/TD]
[TD]Phase 1 Growth[/TD]
[TD]Phase 2[/TD]
[TD]Phase 2 Growth[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD="align: right"]2,320,166[/TD]
[TD="align: right"]-35.84%[/TD]
[TD="align: right"]606,811[/TD]
[TD="align: right"]-23.21%[/TD]
[TD="align: right"]2,926,977[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD="align: right"]3,382,372[/TD]
[TD="align: right"]-17.51%[/TD]
[TD="align: right"]526,319[/TD]
[TD="align: right"]-33.39%[/TD]
[TD="align: right"]3,908,691[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD="align: right"]4,113,943[/TD]
[TD="align: right"]-1.61%[/TD]
[TD="align: right"]350,857[/TD]
[TD="align: right"]-62.52%[/TD]
[TD="align: right"]4,464,800[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD="align: right"]3,827,785[/TD]
[TD="align: right"]-8.41%[/TD]
[TD="align: right"]1,723,212[/TD]
[TD="align: right"]56.91%[/TD]
[TD="align: right"]5,550,997[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD="align: right"]3,158,189[/TD]
[TD="align: right"]-32.32%[/TD]
[TD="align: right"]1,066,162[/TD]
[TD="align: right"]-4.68%[/TD]
[TD="align: right"]4,224,351[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD="align: right"]2,979,606[/TD]
[TD="align: right"]-4.53%[/TD]
[TD="align: right"]807,243[/TD]
[TD="align: right"]7.05%[/TD]
[TD="align: right"]3,786,849[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD="align: right"]1,920,709[/TD]
[TD="align: right"]-40.82%[/TD]
[TD="align: right"]604,977[/TD]
[TD="align: right"]21.15%[/TD]
[TD="align: right"]2,525,686[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the second tab, the growth columns are calculated. In both tabs the total column is calculated. But, in both tabs, the Phase 1 and Phase 2 columns are manually entered data.

Now, here's the issue that I'm running into. I'm trying to calculate a sum from the Phase 1 and Phase 2 columns for the first (FY16) tab, but ONLY for those months that have a value in the FY17 tab. So, in the example above, the sum on the FY16 tab would only include June through December since those are the only months with data in the FY17 tab.

So, I'm trying to use a SUMIF formula.

For the Phase 1 column, I am using

=SUMIF(CurrentYear[Phase 1],"<>",PreviousYear[Phase 1])

For the Phase 2 column, I am using

=SUMIF(CurrentYear[Phase 2],"<>",PreviousYear[Phase 2])

As you can see, both formulas are identical with the exception of the column names.

For the Phase 2 column, I am getting a result of 5,986,908 which I would expect as that is the total from the FY16 tab for June through December of the Phase 2 column.

However, for the Phase 1 column, I am getting a result of 21,702,770 which is NOT the total for June through December from the FY16 tab but is instead the total of the Phase 1 column from the FY17 tab!

I have fought with this now for days and I cannot fix this behavior. SUMIF is not behaving as I would expect it to.

Does anyone have any ideas they might be able to throw my way that can help me understand what is going on here?

Thank you!

 

[live_excel]

Try to do like this one hope it will work,

=SUMIF(H:H,"<>",B:B)

H:H for FY17 Phase 1
B:B for FY16 Phase 1

 

[overlordspock]

^ I'm not 100% sure what you're asking me to do (there is no column H), but if I change the reference to the entire worksheet column B, I just get nonsense numbers back.

 

[live_excel]

I think here's you result but i don't know why you get non scence result.
Screenshot by Lightshot

 

[live_excel]

Need more help? Visit excelchat for free, instant match, personalized help from paid certified experts in Excel.

 

[overlordspock]

That number in your screenshot is the number I should be getting. But I don't.

Keep in mind, these two tables are on two different tabs and are named tables. I don't know if that makes a difference.

 

[live_excel]

Please visit ExcelChat for free . Experts will give you live support.

 

[overlordspock]

Ah. I forgot that my tables have a total row on them as well that I didn't include in the example above. That's why I was getting nonsense numbers back: it was including the values in the total row.

When I turn the total rows off so it's just the data in the tables and replace my formula with

=SUMIF(B:B,"<>",'FY16'!B:B)

, it gives me the same incorrect result: 21,702,770

 

[live_excel]

Please check this on same cell =SUMIF('FY16'!B:B,"<>",'FY17'!B:B)

 

[overlordspock]

I'm sorry, but it's the exact same result.