=sumproduct

erutherford

Active Member
Joined
Dec 19, 2016
Messages
312
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
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?
 
Last edited:

erutherford

Active Member
Joined
Dec 19, 2016
Messages
312
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
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

Active Member
Joined
Dec 19, 2016
Messages
312
The formula would just display in the cell and not function
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
You are welcome.
Once I knew what you were seeing, it became evident right away what the problem was.
 

Forum statistics

Threads
1,084,753
Messages
5,379,663
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

Top