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

DataMonkette

New Member
Joined
Jul 3, 2008
Messages
20
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:

=SUMIF(status,"",asking_price)

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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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..
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top