formula result valid, but not appearing on spreadsheet

[MZ]

Very strange problem...

I type SUMPRODUCT and then hit the "=" to bring up the wizard. I enter the
parameters and it gives me the correct answer at the bottom of the box.
Then when I hit ok, it gives me 0 in the actual spreadsheet. I click on the
0, check the formula, press "=" to bring up the wizard again and the formula
result is still correct.

So why isn't the result showing up on the spreadsheet??

 

[Guest]

What do you have for your formula?

 

[MZ]

What do you have for your formula?

=SUMPRODUCT(IF(data!R2C6:R130C6=RC1,data!R2C5:R130C5,0),IF(data!R2C3:R130C3=R1C,1,0),IF(data!R2C4:R130C4=R1C[1],1,0))

"data" is another worksheet.

 

[Niek Otten]

I don't know about SUMPRODUCT specifically, but it does happen that the function wizard gives correct results and the cell
doesn't. In all cases I know of one or more of the arguments that should be numeric, were actually text.
Cells may look like numbers, but be text for Excel. Just formatting as number doesn't help; format AND re-enter the value (F2,
ENTER).
Of course it is annoying and misleading that the preview doesn't match the actual result.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Very strange problem...
|
| I type SUMPRODUCT and then hit the "=" to bring up the wizard. I enter the
| parameters and it gives me the correct answer at the bottom of the box.
| Then when I hit ok, it gives me 0 in the actual spreadsheet. I click on the
| 0, check the formula, press "=" to bring up the wizard again and the formula
| result is still correct.
|
| So why isn't the result showing up on the spreadsheet??
|
|

 

[MZ]

I don't know about SUMPRODUCT specifically, but it does happen that the
function wizard gives correct results and the cell
doesn't. In all cases I know of one or more of the arguments that should
be numeric, were actually text.
Cells may look like numbers, but be text for Excel. Just formatting as
number doesn't help; format AND re-enter the value (F2,
ENTER).
Of course it is annoying and misleading that the preview doesn't match the
actual result.

I've isolated it to just the first term in the SUMPRODUCT.

If I enter the following:

=SUM(IF(data!R2C6:R130C6=RC1,1,0))

I get 0 in the cell, but 4 in the preview.

The first term in the if statement is text, and so is the element in RC1.
They're supposed to be. But those aren't the values being returned. 1 and
0 are.

This doesn't make any sense!

 

[MZ]

I've isolated it to just the first term in the SUMPRODUCT.

If I enter the following:

=SUM(IF(data!R2C6:R130C6=RC1,1,0))

I get 0 in the cell, but 4 in the preview.

The first term in the if statement is text, and so is the element in RC1.
They're supposed to be. But those aren't the values being returned. 1
and 0 are.

This doesn't make any sense!

Figured it out. For some reason, I needed to ctrl-shift-enter when I
entered it.