SUMPRODUCT not working

A

Ang

I have a formula using SUMPRODUCT which was working and, unfortunately, is no
longer working and I don't know why. Instead of real values, all I get now
is all zeros. I haven't changed the formula. The data sheet where the info
is, changes daily but shouldn't affect my formula, correct? I looking at
HELP in Excel and I'm wondering if this has anything to do with my issue:

SUMPRODUCT treats array entries that are not numeric as if they were zeros.

Any ideas out there on why this wouldn't be working any longer?
Thx for your expertise!
 
A

Ang

Labor download column O = day of month
ReportA6=day of month

Labor download column I = department
Labor report columnR -= department

Labor download column K = dollars

(SUMPRODUCT(('Labor Download'!$O$2:$O$12000=Report!$A$6)*('Labor
Download'!$I$2:$I$12000='Labor Report'!R15)*('Labor Download'!$K$2:$K$12000)))


same formula but different column ref for hours

Help?
 
B

Barb Reinhardt

I'd try this:

=SUMPRODUCT(--('Labor Download'!$O$2:$O$12000=Report!$A$6),--('Labor
Download'!$I$2:$I$12000='Labor Report'!R15),('Labor Download'!$K$2:$K$12000))
 
T

T. Valko

If the formula worked before and...
The data sheet where the info is, changes daily
but shouldn't affect my formula, correct?
Click to expand...

That's where I'd look for the problem. The data changes daily how? Is it
imported from another application? The usual culprits are unseen characters
like leading/trailing spaces, HTML "junk" like char 160's, numbers formatted
as TEXT.

Biff
 
D

dq

Ang,

Perhepas a stupid suggestion, but dit you enter the formula with Ctrl
+Shift+Enter ? SUMPRODUCT is an array formula and those always need to
be entered with Ctrl+Shift+Enter in stead of just Enter.

DQ
 
T

T. Valko

SUMPRODUCT works with arrays but does not need to be array entered *unless*
you're using arguments that require array entry like IF or TRANSPOSE.

Biff
 
Top