Sumif Bug?

[mistereous]

I'm 99% sure I've stumbled across a bug in excel.

The formula in question is

=-SUMIF('Data Set'!$AO:$AO,"multijantn0",'Data Set'!$O:$O)

This formula should calculate the same no matter where it is in the spreadsheet. Everything is anchored to look at the same columns

I've got a series of cross-referenced multinested columns which are essentially adding fields to a database and then pulling the data forward into a report using a combination of sumifs, countifs, etc.

During a routine review of the report I saw a negative number where there should be none so I started to investigate.

I checked the data set and it has a value of $3,000 versus the formula calculation of $11,000. I removed any cell specific references and pasted the formula at the bottom of the data set (see above). It matches the $3,000.

For curiousity I started pasting the formula onto several different worksheets within the same workbook. Each worksheet calculates a different result for the same formula. It's kind of like 2 + 2 = 4 on one sheet 7 on another sheet and 11 on the next sheet. Anyone else hear of or know of anything similar occuring?

[daddylonglegs]

Your "sum range" doesn't contain a sheet name so it'll use column O on the sheet with the formula so if you use this formula on sheet1

=-SUMIF('Data Set'!$AO:$AO,"multijantn0",$O:$O)

it will sum column O in sheet1 depending on values in Data Set....but if you put the same formula in sheet2 it'll sum sheet 2 values from column O.....so it's certainly possible to get different results on different sheets. If you want to sum column O in Data Set worksheet then change to

=-SUMIF('Data Set'!$AO:$AO,"multijantn0",'Data Set'!$O:$O)

That formula should give you the same result wherever it is in the workbook

[mistereous]

Typo on my part the formula should read

=-SUMIF('Data Set'!$AO:$AO,"multijantn0",'Data Set'!$O:$O)

I changed the original post as well for clarification purposes.

[andrewshimmin]

I have the same problem - a SUMIF formula works fine in one (any one of several) tab, but doesn't work in another tab - it returns the wrong result.
Even more mysteriously, if I change the reference from values in C to values in D, it returns #VALUE!
Any thoughts?

[daddylonglegs]

Can you post the formula you're using?