Date in Formula not working

[Dana]

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

[bpeltzer]

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

[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

[Dave Peterson]

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

[Dana]

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
>

[Peo Sjoblom]

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
>>

[Dana]

This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?

Thanks also to Dave and Bruce!!!
--
Dana


"Peo Sjoblom" wrote:

> 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
> >>
>
>

[Dave Peterson]

=int() returns the whole part of the number. It ignores the fraction.

=int(3.5) = 3

With dates/times, days are whole numbers and times are fractions.


Dana wrote:
>
> This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?
>
> Thanks also to Dave and Bruce!!!
> --
> Dana
>
> "Peo Sjoblom" wrote:
>
> > 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
> > >>
> >
> >

--

Dave Peterson

[Dana]

Thanks again! This was extremely helpful!

--
Dana


"Dave Peterson" wrote:

> =int() returns the whole part of the number. It ignores the fraction.
>
> =int(3.5) = 3
>
> With dates/times, days are whole numbers and times are fractions.
>
>
> Dana wrote:
> >
> > This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?
> >
> > Thanks also to Dave and Bruce!!!
> > --
> > Dana
> >
> > "Peo Sjoblom" wrote:
> >
> > > 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
> > > >>
> > >
> > >
>
> --
>
> Dave Peterson
>

[Dana]

How would I use this same formula if I wanted to use an "or" statement with
multiple product names, i.e., count the number of times "product 1" or
"product 2" or "product 3" appears in range "worksheet1!B4:B279" for the date
specified in range ""--(INT(worksheet1!L4:L279)=DATE(2006,1,3)))?

The formula below only counts one product in that range.

Thanks again!

--
Dana


"Dana" wrote:

> Thanks again! This was extremely helpful!
>
> --
> Dana
>
>
> "Dave Peterson" wrote:
>
> > =int() returns the whole part of the number. It ignores the fraction.
> >
> > =int(3.5) = 3
> >
> > With dates/times, days are whole numbers and times are fractions.
> >
> >
> > Dana wrote:
> > >
> > > This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?
> > >
> > > Thanks also to Dave and Bruce!!!
> > > --
> > > Dana
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > 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
> > > > >>
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

[Dave Peterson]

How about:

=SUMPRODUCT((worksheet1!B4:B279={"product 1","product 2","product 3"})
*(INT(worksheet1!L4:L279)=DATE(2006,1,3)))



Dana wrote:
>
> How would I use this same formula if I wanted to use an "or" statement with
> multiple product names, i.e., count the number of times "product 1" or
> "product 2" or "product 3" appears in range "worksheet1!B4:B279" for the date
> specified in range ""--(INT(worksheet1!L4:L279)=DATE(2006,1,3)))?
>
> The formula below only counts one product in that range.
>
> Thanks again!
>
> --
> Dana
>
> "Dana" wrote:
>
> > Thanks again! This was extremely helpful!
> >
> > --
> > Dana
> >
> >
> > "Dave Peterson" wrote:
> >
> > > =int() returns the whole part of the number. It ignores the fraction.
> > >
> > > =int(3.5) = 3
> > >
> > > With dates/times, days are whole numbers and times are fractions.
> > >
> > >
> > > Dana wrote:
> > > >
> > > > This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?
> > > >
> > > > Thanks also to Dave and Bruce!!!
> > > > --
> > > > Dana
> > > >
> > > > "Peo Sjoblom" wrote:
> > > >
> > > > > 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
> > > > > >>
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

[Dana]

Thanks Dave! That worked!

If you have some extra time, could you explain these formulas to me? Even
though they're working and doing exactly what I need them to do, I want to
make sure I understand them.

I'm used to using "Countif" and "Sumif", but I'm not entirely familiar with
Sumproduct, particularly with the use of the double hyphens, etc. Any
explanation as to the way this formula is built would be extremely helpful.

Thanks again for all your help!! This site is fantastic, and has saved my
company a lot of time!!!

--
Dana


"Dave Peterson" wrote:

> How about:
>
> =SUMPRODUCT((worksheet1!B4:B279={"product 1","product 2","product 3"})
> *(INT(worksheet1!L4:L279)=DATE(2006,1,3)))
>
>
>
> Dana wrote:
> >
> > How would I use this same formula if I wanted to use an "or" statement with
> > multiple product names, i.e., count the number of times "product 1" or
> > "product 2" or "product 3" appears in range "worksheet1!B4:B279" for the date
> > specified in range ""--(INT(worksheet1!L4:L279)=DATE(2006,1,3)))?
> >
> > The formula below only counts one product in that range.
> >
> > Thanks again!
> >
> > --
> > Dana
> >
> > "Dana" wrote:
> >
> > > Thanks again! This was extremely helpful!
> > >
> > > --
> > > Dana
> > >
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =int() returns the whole part of the number. It ignores the fraction.
> > > >
> > > > =int(3.5) = 3
> > > >
> > > > With dates/times, days are whole numbers and times are fractions.
> > > >
> > > >
> > > > Dana wrote:
> > > > >
> > > > > This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?
> > > > >
> > > > > Thanks also to Dave and Bruce!!!
> > > > > --
> > > > > Dana
> > > > >
> > > > > "Peo Sjoblom" wrote:
> > > > >
> > > > > > 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
> > > > > > >>
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Dana wrote:
>
> Thanks Dave! That worked!
>
> If you have some extra time, could you explain these formulas to me? Even
> though they're working and doing exactly what I need them to do, I want to
> make sure I understand them.
>
> I'm used to using "Countif" and "Sumif", but I'm not entirely familiar with
> Sumproduct, particularly with the use of the double hyphens, etc. Any
> explanation as to the way this formula is built would be extremely helpful.
>
> Thanks again for all your help!! This site is fantastic, and has saved my
> company a lot of time!!!
>
> --
> Dana
>
> "Dave Peterson" wrote:
>
> > How about:
> >
> > =SUMPRODUCT((worksheet1!B4:B279={"product 1","product 2","product 3"})
> > *(INT(worksheet1!L4:L279)=DATE(2006,1,3)))
> >
> >
> >
> > Dana wrote:
> > >
> > > How would I use this same formula if I wanted to use an "or" statement with
> > > multiple product names, i.e., count the number of times "product 1" or
> > > "product 2" or "product 3" appears in range "worksheet1!B4:B279" for the date
> > > specified in range ""--(INT(worksheet1!L4:L279)=DATE(2006,1,3)))?
> > >
> > > The formula below only counts one product in that range.
> > >
> > > Thanks again!
> > >
> > > --
> > > Dana
> > >
> > > "Dana" wrote:
> > >
> > > > Thanks again! This was extremely helpful!
> > > >
> > > > --
> > > > Dana
> > > >
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > =int() returns the whole part of the number. It ignores the fraction.
> > > > >
> > > > > =int(3.5) = 3
> > > > >
> > > > > With dates/times, days are whole numbers and times are fractions.
> > > > >
> > > > >
> > > > > Dana wrote:
> > > > > >
> > > > > > This worked!!!! Thank you so much! Out of curiosity, what does the "int" do?
> > > > > >
> > > > > > Thanks also to Dave and Bruce!!!
> > > > > > --
> > > > > > Dana
> > > > > >
> > > > > > "Peo Sjoblom" wrote:
> > > > > >
> > > > > > > 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
> > > > > > > >>
> > > > > > >
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson