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 |
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 |
PERFECT-awesome
I should come here first, it would have save hours if digging! Thanks! Chrism |
All times are GMT +1. The time now is 01:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com