SUMPRODUCT and format problems (2)

[Chrism]

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")*(D116))

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

 

[Bob Umlas]

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")*(D116))
also, if the values in C are numbers, not text, remove the quotes around
33758 as well.

Bob Umlas
Excel MVP

 

[Chrism]

PERFECT-awesome
I should come here first, it would have save hours if digging!
Thanks!
Chrism