Date in Formula not working

Please help! Can't figure out why this isn't working!

I'm trying to count the occurences of a date in a column, along with another
value in another column (product name). My formula is as follows:

= sum(if(range="productname",if(range=1/03/2006,1,0)))

I'm getting a value of "0" for the answer. If I replace the date above with
another value in another column (text), it appears with the correct answer.
But the correct answer is only appearing in the formula builder (= sign at
the top left of the page), but it still shows a 0 in the result cell. So I
guess the problem is twofold.

Any help would be appreciated!
--
Dana

I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3)))
(You didn't indicate what your ranges are. One limitation of sumproduct is
that you can't use entire columns such as A:A; you have to include row
qualifiers, even if they select the entire column: A1:A65536).
--Bruce

"Dana" wrote:

> Please help! Can't figure out why this isn't working!
>
> I'm trying to count the occurences of a date in a column, along with another
> value in another column (product name). My formula is as follows:
>
> = sum(if(range="productname",if(range=1/03/2006,1,0)))
>
> I'm getting a value of "0" for the answer. If I replace the date above with
> another value in another column (text), it appears with the correct answer.
> But the correct answer is only appearing in the formula builder (= sign at
> the top left of the page), but it still shows a 0 in the result cell. So I
> guess the problem is twofold.
>
> Any help would be appreciated!
> --
> Dana

Makes sense, but it didn't work. Still getting 0 as the result.

My formula is:

=sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3)))
--
Dana


"bpeltzer" wrote:

> I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3)))
> (You didn't indicate what your ranges are. One limitation of sumproduct is
> that you can't use entire columns such as A:A; you have to include row
> qualifiers, even if they select the entire column: A1:A65536).
> --Bruce
>
> "Dana" wrote:
>
> > Please help! Can't figure out why this isn't working!
> >
> > I'm trying to count the occurences of a date in a column, along with another
> > value in another column (product name). My formula is as follows:
> >
> > = sum(if(range="productname",if(range=1/03/2006,1,0)))
> >
> > I'm getting a value of "0" for the answer. If I replace the date above with
> > another value in another column (text), it appears with the correct answer.
> > But the correct answer is only appearing in the formula builder (= sign at
> > the top left of the page), but it still shows a 0 in the result cell. So I
> > guess the problem is twofold.
> >
> > Any help would be appreciated!
> > --
> > Dana

You may want to copy the formula directly from the formula bar and post into the
message.

I would bet that "productname" doesn't appear in b4:B279 of worksheet1

or you don't have any January 1, 2006 in L4:L279 (or you don't really have dates
in L4:L279--maybe it's just text that looks like a date).

Dana wrote:
>
> Makes sense, but it didn't work. Still getting 0 as the result.
>
> My formula is:
>
> =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3)))
> --
> Dana
>
> "bpeltzer" wrote:
>
> > I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3)))
> > (You didn't indicate what your ranges are. One limitation of sumproduct is
> > that you can't use entire columns such as A:A; you have to include row
> > qualifiers, even if they select the entire column: A1:A65536).
> > --Bruce
> >
> > "Dana" wrote:
> >
> > > Please help! Can't figure out why this isn't working!
> > >
> > > I'm trying to count the occurences of a date in a column, along with another
> > > value in another column (product name). My formula is as follows:
> > >
> > > = sum(if(range="productname",if(range=1/03/2006,1,0)))
> > >
> > > I'm getting a value of "0" for the answer. If I replace the date above with
> > > another value in another column (text), it appears with the correct answer.
> > > But the correct answer is only appearing in the formula builder (= sign at
> > > the top left of the page), but it still shows a 0 in the result cell. So I
> > > guess the problem is twofold.
> > >
> > > Any help would be appreciated!
> > > --
> > > Dana

--

Dave Peterson

The formula works when I use anything but the dates. If I replace the date
and use another column with text, it works!

When my cursor goes over the cell with the first date in it (L4), the
formula bar reads "1/3/2006 6:34:00 PM". It's exported data from a CRM tool.
When I click on "Format - Cells", it shows it as a "date" in the "number"
tab.
--
Dana


"Dave Peterson" wrote:

> You may want to copy the formula directly from the formula bar and post into the
> message.
>
> I would bet that "productname" doesn't appear in b4:B279 of worksheet1
>
> or you don't have any January 1, 2006 in L4:L279 (or you don't really have dates
> in L4:L279--maybe it's just text that looks like a date).
>
> Dana wrote:
> >
> > Makes sense, but it didn't work. Still getting 0 as the result.
> >
> > My formula is:
> >
> > =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3)))
> > --
> > Dana
> >
> > "bpeltzer" wrote:
> >
> > > I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3)))
> > > (You didn't indicate what your ranges are. One limitation of sumproduct is
> > > that you can't use entire columns such as A:A; you have to include row
> > > qualifiers, even if they select the entire column: A1:A65536).
> > > --Bruce
> > >
> > > "Dana" wrote:
> > >
> > > > Please help! Can't figure out why this isn't working!
> > > >
> > > > I'm trying to count the occurences of a date in a column, along with another
> > > > value in another column (product name). My formula is as follows:
> > > >
> > > > = sum(if(range="productname",if(range=1/03/2006,1,0)))
> > > >
> > > > I'm getting a value of "0" for the answer. If I replace the date above with
> > > > another value in another column (text), it appears with the correct answer.
> > > > But the correct answer is only appearing in the formula builder (= sign at
> > > > the top left of the page), but it still shows a 0 in the result cell. So I
> > > > guess the problem is twofold.
> > > >
> > > > Any help would be appreciated!
> > > > --
> > > > Dana
>
> --
>
> Dave Peterson
>

Try

=SUMPRODUCT(--(worksheet1!B4:B279="productname"),--(INT(worksheet1!L4:L279)=DATE(2006,1,3)))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dana" <[email protected]> wrote in message
news:[email protected]...
> The formula works when I use anything but the dates. If I replace the date
> and use another column with text, it works!
>
> When my cursor goes over the cell with the first date in it (L4), the
> formula bar reads "1/3/2006 6:34:00 PM". It's exported data from a CRM
> tool.
> When I click on "Format - Cells", it shows it as a "date" in the "number"
> tab.
> --
> Dana
>
>
> "Dave Peterson" wrote:
>
>> You may want to copy the formula directly from the formula bar and post
>> into the
>> message.
>>
>> I would bet that "productname" doesn't appear in b4:B279 of worksheet1
>>
>> or you don't have any January 1, 2006 in L4:L279 (or you don't really
>> have dates
>> in L4:L279--maybe it's just text that looks like a date).
>>
>> Dana wrote:
>> >
>> > Makes sense, but it didn't work. Still getting 0 as the result.
>> >
>> > My formula is:
>> >
>> > =sumproduct(--('worksheet1'!b4:b279="productname"),--('worksheet1'!L4:L279=date(2006,1,3)))
>> > --
>> > Dana
>> >
>> > "bpeltzer" wrote:
>> >
>> > > I'd use =sumproduct(--(range="productname"),--(range=date(2006,1,3)))
>> > > (You didn't indicate what your ranges are. One limitation of
>> > > sumproduct is
>> > > that you can't use entire columns such as A:A; you have to include
>> > > row
>> > > qualifiers, even if they select the entire column: A1:A65536).
>> > > --Bruce
>> > >
>> > > "Dana" wrote:
>> > >
>> > > > Please help! Can't figure out why this isn't working!
>> > > >
>> > > > I'm trying to count the occurences of a date in a column, along
>> > > > with another
>> > > > value in another column (product name). My formula is as follows:
>> > > >
>> > > > = sum(if(range="productname",if(range=1/03/2006,1,0)))
>> > > >
>> > > > I'm getting a value of "0" for the answer. If I replace the date
>> > > > above with
>> > > > another value in another column (text), it appears with the correct
>> > > > answer.
>> > > > But the correct answer is only appearing in the formula builder (=
>> > > > sign at
>> > > > the top left of the page), but it still shows a 0 in the result
>> > > > cell. So I
>> > > > guess the problem is twofold.
>> > > >
>> > > > Any help would be appreciated!
>> > > > --
>> > > > Dana
>>
>> --
>>
>> Dave Peterson
>>