Formula works, but doesnt display output in cell

[Dmn9]

Hello world,

I am having a pickle of a time with my excel 2007. What is happening is that i have a formula that works. When i click the fx bar and view it through the formula wizard it displays the correct calculation amount. However in the cell it self it displays 0.

I've racked my brain trying to fiddle with text formats and finding and replacing "=" but to no avail. Does anyone know of a solution to this? Perusing through google i've found the problem is common but no one was able to answer it.

Please help!

Thank you so much!

 

[Scott Huish]

If you format the cell as General, what happens?

What is your formula?

 

[Dmn9]

Hi Hotpepper the formula is as shown below.

=SUM(IF(Worksheet!$G$2:$G$10020=1990,Worksheet!$I$2:$I$10020,0),(IF(Worksheet!$G$2:$G$10020=1991,Worksheet!$I$2:$I$10020,0)),(IF(Worksheet!$G$2:$G$10020=1992,Worksheet!$I$2:$I$10020,0)),(IF(Worksheet!$G$2:$G$10020=1993,Worksheet!$I$2:$I$10020,0)),(IF(Worksheet!$G$2:$G$10020=1994,Worksheet!$I$2:$I$10020,0)),(IF(Worksheet!$G$2:$G$10020=1995,Worksheet!$I$2:$I$10020,0)))


i've cleared the formats out and changed them to accounting and still no dice. Its strange because in the formula wizard it shows the correct calculation however the cell displays 0 still.

 

[Scott Huish]

You need to use CTRL-SHIFT-ENTER on that formula rather than just Enter.

This is much simpler though. You still need CTRL-SHIFT-ENTER

=SUM(IF(ISNUMBER(MATCH(Worksheet!$G$2:$G$10020,{1990,1991,1992,1993,1994,1995},0)),Worksheet!$I$2:$I$10020))

 

[Dmn9]

Thanks for your reply. I tried using the ctrl-shift-enter as its an array formula but that didn't make a change.
Do you think the OR operator would be a better fit?

 

[Scott Huish]

Try the formula I provided. Are the values in column I numbers stored as text?

 

[Dmn9]

You sir or madame make the internet a better place ! Thank you soooooo much! It worked like a charm.

 

[Cease]

Hey Dmn9,

Really it might be a lot of different things, but right off I'd expect it to be something with the format.

What is the correct value that's shown in the wizard?

Also, could you elaborate a little more as to what you want the function to do?

I feel there's going to be an easier way through another function, but I'm not sure with just that formula itself.

One suggestion with all of the If statements having the same "True" response you could then consolidate the function down some to:

=IF(OR(Worksheet!$G$2:$G$10020=1990,Worksheet!$G$2:$G$10020=1991,Worksheet!$G$2:$G$10020=1992,Worksheet!$G$2:$G$10020=1993,Worksheet!$G$2:$G$10020=1994,Worksheet!$G$2:$G$10020=1995),Worksheet!$I$2:$I$10020,0)

There's a clearer way with arrays, but this clears it up a little.

 

[Scott Huish]

That won't return the correct result.