| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT not working | Excel Discussion (Misc queries) | |||
| sumproduct not working | Excel Worksheet Functions | |||
| Sumproduct Not Working | Excel Worksheet Functions | |||
| sumproduct not working | Excel Worksheet Functions | |||
| =SUMPRODUCT not working | Excel Worksheet Functions | |||
Linear Mode
