Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT and format problems (2)
I am working on a spreadsheet where the data is imported from Access.
The data includes employee numbers where on Access they are zero-filled to 5 digits, so Ee# 77 is actually 00077. Using SUMIF and SUMPRODUCT formulae, even with changing the cell format, the formula does not 'see' the number unless I retype the number (with over 200 employees on my list this is more than time consuming). Is there a way to format either the formulae (SUMPRODUCT & SUMIF) to 'see' the digits, or format the cell? Also--and my biggest quandry: I am following a SUMPRODUCT example exactly from text and coming up with a "0" answer when I know it's not correct. The spreadsheet is 587 rows long. A B C D empno date audempno units 1 77 2/28/05 33758 9 2 4066 2/28/05 33758 8 3 10058 2/28/05 33758 15 4 16036 2/28/05 33758 10 5 17356 2/28/05 33758 10 6 17642 2/28/05 33758 12 7 26874 2/28/05 33758 32 8 32280 2/28/05 33758 14 9 49747 2/28/05 33758 22 10 64740 2/28/05 33758 5 11 70446 2/28/05 33758 12 12 77 3/2/05 33758 10 13 9039 3/2/05 33758 10 14 11420 3/2/05 33758 10 15 17406 3/2/05 33758 13 16 17874 3/2/05 33758 16 What I'm trying to do is sum the total units (D) done by AUDEMP (C) for each date (B). My formula looks like: =SUMPRODUCT((B1:B16="2/28/05")*(C1:C16="33758")*(D1:D16)) and I know about the "ctrl-shift-enter" to correctly enter an array formula. I have shrunk my spreadsheet considerably here, it runs from B2 to 0587. I have worked the "#VALUE" issues but can't see why it sums "0", it's possible it's related to the 1st issue of formatting, I appreciate any help. I've been digging on-line and in j-walk's Bible and haven't come across any answers. Chrism |
#2
|
|||
|
|||
My guess is that the dates in B are real excel dates, not text strings, so
nothing matches up with "2/28/05". Try this: =SUMPRODUCT((B1:B16=Datevalue("2/28/05"))*(C1:C16="33758")*(D1:D16)) also, if the values in C are numbers, not text, remove the quotes around 33758 as well. Bob Umlas Excel MVP "Chrism" wrote in message oups.com... I am working on a spreadsheet where the data is imported from Access. The data includes employee numbers where on Access they are zero-filled to 5 digits, so Ee# 77 is actually 00077. Using SUMIF and SUMPRODUCT formulae, even with changing the cell format, the formula does not 'see' the number unless I retype the number (with over 200 employees on my list this is more than time consuming). Is there a way to format either the formulae (SUMPRODUCT & SUMIF) to 'see' the digits, or format the cell? Also--and my biggest quandry: I am following a SUMPRODUCT example exactly from text and coming up with a "0" answer when I know it's not correct. The spreadsheet is 587 rows long. A B C D empno date audempno units 1 77 2/28/05 33758 9 2 4066 2/28/05 33758 8 3 10058 2/28/05 33758 15 4 16036 2/28/05 33758 10 5 17356 2/28/05 33758 10 6 17642 2/28/05 33758 12 7 26874 2/28/05 33758 32 8 32280 2/28/05 33758 14 9 49747 2/28/05 33758 22 10 64740 2/28/05 33758 5 11 70446 2/28/05 33758 12 12 77 3/2/05 33758 10 13 9039 3/2/05 33758 10 14 11420 3/2/05 33758 10 15 17406 3/2/05 33758 13 16 17874 3/2/05 33758 16 What I'm trying to do is sum the total units (D) done by AUDEMP (C) for each date (B). My formula looks like: =SUMPRODUCT((B1:B16="2/28/05")*(C1:C16="33758")*(D1:D16)) and I know about the "ctrl-shift-enter" to correctly enter an array formula. I have shrunk my spreadsheet considerably here, it runs from B2 to 0587. I have worked the "#VALUE" issues but can't see why it sums "0", it's possible it's related to the 1st issue of formatting, I appreciate any help. I've been digging on-line and in j-walk's Bible and haven't come across any answers. Chrism |
#3
|
|||
|
|||
PERFECT-awesome
I should come here first, it would have save hours if digging! Thanks! Chrism |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT??? | Excel Worksheet Functions | |||
Format cells as date | Excel Discussion (Misc queries) | |||
Sumproduct help needed! | Excel Worksheet Functions | |||
SUMPRODUCT ON TIMES | Excel Worksheet Functions | |||
SUMPRODUCT using offset from ROW if X marks the spot | Excel Worksheet Functions |