SUMIF Comparison Treats a Blank Cell on Subtotal Row as equal to a Text String Incorrectly

[lmoseley7]

First let me describe my data. Columns A:V are in use and the data goes down just under 500 rows. The data represents income received on securities and is broken down by several data points such as account, security identifier, major industry name, minor industry name, etc. The data has been subtotaled by a column that contains a formula that looks at the data columns and returns a string based upon the criteria. I'm trying to calculate the same values without having to have the string formula, subtotaling the data and then using VLOOKUP to find the subtotal of each grouping. Overall I've been very successful, but the issue comes up when I have to test the same column for two different text strings. After Googling for a potential solution, I came up with the following formula:

{=SUM(IF((ss_MajIN=$I$5),1,0)*ss_BNIA)}

ss_MajIN is a named range that contains text strings detailing the Major Industry Name of a given security.
ss_BNIA is a named range that contains the Income Amounts to sum.
I5 is a cell that contains the text "YANKEE" which is one of the values found in the Major Industry Name column.

In this formula example, I'm only testing one possibility because testing both was not working either.

The result of the formula above is that it returns the correct amount based upon finding the string "YANKEE" in ss_MajIN but it also includes all the subtotaled amounts from the data as well, even though the cell in the ss_MajIN column of the subtotaled rows is blank. Stepping through the formula evaluator, it gives a TRUE result when comparing a "0" to "YANKEE".

Does anyone know why this might be happening? I've tried looking at a subset of the data by changing the ranges to be a smaller group of rows so that the formula evaluator is easier to follow and I get the same results. When I copied a subset of the data to another sheet and rewrote the formulas, I get the correct results. That only serves to frustrate me more. Hopefully someone can help. My work won't allow me to post to a drop-box or similar account so if the actual workbook needs to be viewed, I'll have to email it, so please PM me if you need to see the workbook.

Thanks

 

[lmoseley7]

Changing the formula to only look at the subtotal row, the formula evaluates to the following:

SUM(IF((0="YANKEE"),1,0)*D241)

The next step of the evaluate formula returns the following:

SUM(IF((TRUE),1,0)*D241)

How does 0=YANKEE?

 

[lmoseley7]

Adding any text in the blank subtotal row cell does appear to fix the issue. The data is pulled from over the web through an interactive spreadsheet and then subtotaled. Even though I can't see anything in the cell, Excel must be evaluating the 0 as False and anything not 1 or TRUE as false. That's the only thing I can figure. I copied the sheet and removed the subtotals and was able to calculate the correct value on that copied sheet. This is driving me batty.

 

[lmoseley7]

Ok, so after a weekend away from the spreadsheet I came back this morning with fresh eyes. I took the simplest version of the formula that was returning bad results and copied it and pasted it on the sheet where the data resides. When there the formula worked as expected. I then decided to insert a new sheet into the workbook and copy the information from the original sheet into this new sheet to see if the problems with the formulas remained. They did not. I went back to the original sheet and selected all cells and chose Clear All from the Editing group of the Home ribbon and then pasted the formulas back and the problems persisted. I then selected all cells and chose Delete from the Cells group of the Home ribbon. After pasting the formulas back, the problems returned. There is something up with this sheet. I've never seen anything like this.