SUMPRODUCT not working
 

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!

SUMPRODUCT not working
 

Might help to post your formula

--
Don Guillett
SalesAid Software

"Ang" wrote in message
...
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!

SUMPRODUCT not working
 

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?

"Don Guillett" wrote:

Might help to post your formula

--
Don Guillett
SalesAid Software

"Ang" wrote in message
...
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!

SUMPRODUCT not working
 

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))


"Ang" wrote:

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?

"Don Guillett" wrote:

Might help to post your formula

--
Don Guillett
SalesAid Software

"Ang" wrote in message
...
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!

SUMPRODUCT not working
 

Sadly, I'm still getting a zero result which is wrong....any suggestions? Thx!

"Barb Reinhardt" wrote:

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))


"Ang" wrote:

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?

"Don Guillett" wrote:

Might help to post your formula

--
Don Guillett
SalesAid Software

"Ang" wrote in message
...
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!

SUMPRODUCT not working
 

If the formula worked before and...

The data sheet where the info is, changes daily
but shouldn't affect my formula, correct?

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

"Ang" wrote in message
...
Sadly, I'm still getting a zero result which is wrong....any suggestions?
Thx!

"Barb Reinhardt" wrote:

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))


"Ang" wrote:

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?

"Don Guillett" wrote:

Might help to post your formula

--
Don Guillett
SalesAid Software

"Ang" wrote in message
...
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!

SUMPRODUCT not working
 

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

SUMPRODUCT not working
 

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

"dq" wrote in message
ps.com...
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