=sumproduct

[erutherford]

hate last minute change requests, but

The code works fine on the worksheet where the data resides, see below

<code>=SUMPRODUCT((I3:I51="S")*(J3:M51=2))</code>


but won't work when code is on another worksheet

<code>=SUMPRODUCT((Entries!I3:I51="S")*(Entries!J3:M51=2))</code>

what am I missing? Maybe I should take a deep breath!

 

[Joe4]

It works fine for me.
- How is it not working? Are you getting an error or unexpected results?
- Are you sure that you have spelled the sheet name correctly? Anything different, even an "extra" space, will cause it to fail.
- Are your sure that this other sheet is in the same workbook?

 

[erutherford]

Ok I just try it on the worksheet where it should be, using another cell and it works

I just cut it from the test cell and pasted it into the correct cell and its functioning. I did delete a Conditional format that was there, maybe the cause?

Thanks as always for a quick reply!

 

[Joe4]

Conditional Formatting shouldn't have any affect on it.

You didn't say what the issue was, whether you were getting errors or unexpected results.

 

[erutherford]

The formula would just display in the cell and not function

 

[Joe4]

The formula would just display in the cell and not function

That just means that you entered the formula as text, and not as a formula.
That will happen if the column is formatted as text to begin with.
Select the whole column, go to Format Cells, and change the format to General (that is typically the default).
Then when you enter formulas, then will enter as formulas and not text.

 

[erutherford]

the "why" is almost as important as the results!

thanks for pursuing!

 

[Joe4]

You are welcome.
Once I knew what you were seeing, it became evident right away what the problem was.