# SUMIF Question: Why will it work in one workbook, but not the other?

#### DataMonkette

##### New Member
Here's my deal:

I inherited a workbook from a project that my boss worked on at her former company. She'd like to take that exact same workbook and use it for her new project because the same client liked the way it looked. Because the workbook is locked, I've been copying over every single formula by hand (can't even copy/paste from the formula bar), but not really understanding what they do because I'm not really an Excel data monkey. At least not this kind.

Anyway, I've learned much about how everything works in the workbook and every single formula has been copied over faithfully and works in the new book... except for one.

We have one data range (status) that contains information on where in the sales process a given unit is. We have another data range that contains the current selling price for the unit (asking_price). The formula in the cell in question is this:

On the old, locked workbook, there's a value. On my brand, spanking new workbook, I am getting a result of 0.

What does the "" part mean? If I could just find out what the "" part means, then I could perhaps find out why I'm getting a 0 because we're so early in the sales process for the new project that not all the variables are the same.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Jonmo1

##### MrExcel MVP
Welcome to the board...

the "" indicates blank..

so the formula is SUMMING asking_price where status = ""

If it's not working, verify a couple things..

the cells in the Status range are actually blank, no spaces or other strange characters.
the numbers in asking_price are actually numbers, not "Numbers Stored as Text"

to test that, use =isnumber(A1)
Where A1 is one of the numbers in asking_price

If that returns FALSE, they are not really numbers..

Hope that helps..

##### MrExcel MVP
"" means empty/blank...

#### DataMonkette

##### New Member
Welcome to the board...

Thanks! Taking your suggestions in reverse order...

[Check to see if] the numbers in asking_price are actually numbers, not "Numbers Stored as Text"

to test that, use =isnumber(A1)
Where A1 is one of the numbers in asking_price

If that returns FALSE, they are not really numbers..

Hope that helps..

Checked this and they are numbers.

[Also, check and see if] the cells in the Status range are actually blank, no spaces or other strange characters.

There are no blanks in Status, and here's the formula I'm using there:

=IF(AI25>0,"Closed",IF(AH25>0,"Signed",IF(AG25>0,"Out",IF(AF25>0,"Reserved"))))

The result I get here is "FALSE". From AI25 to AF25, those are dates that are entered by hand into the spreadsheet.

On the original spreadsheet, where Inventory hasn't been released, they're using this formula to get a Y/N result:

=IF(ISBLANK(\$E25)=FALSE,IF(ISBLANK(AG\$25)=TRUE,IF(ISBLANK(\$AF25)=TRUE,"Y","N"),"N"),"N")

...which struck me as being needlessly complicated, so on my workbook, I just have Y if we released the Inventory and N if we didn't.

However, I just pasted the old formula into a Status field next to where where I'm getting the "FALSE" result, and it hasn't made any impact or change to the original SUMIF result.

So what do I need to change here?

#### DataMonkette

##### New Member
Why will it work in one workbook, but not the other?

*BUMP*

I'm not sure what else I can add to this thread other than I'm using Excel 2003 and I work in real estate. Can someone help me troubleshoot this issue? Thank you.

#### DataMonkette

##### New Member
Much thanks to user bk who fixed the Status formula! For the record, the fix is this:

=IF(AI17>0,"Closed",IF(AH17>0,"Signed",IF(AG17>0,"Out",IF(AF17>0,"Reserved",""))))

It's that extra "OR" that makes a difference, it seems.

Replies
0
Views
201
Replies
0
Views
546
Replies
0
Views
204
Replies
3
Views
457
Replies
2
Views
404

1,191,187
Messages
5,985,191
Members
439,947
Latest member
fabiannic

### 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.

### Which adblocker are you using?

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

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